Speed up Excel file which contain VBA code

Question

I am using this function in my vba:

Function mergedText(rngMergedCell As Range)

    If rngMergedCell.MergeCells = True Then
        mergedText = rngMergedCell.MergeArea(1, 1)
    Else
        mergedText = rngMergedCell
    End If 

End Function

The function let my formula to read merged cells value. It works very well. However, the excel become so slow.

How can I speed up my excel? Please provide me with step by step answers. I am not good with vba.

Thanks!

Answer 1

Here is a method to retrieve the value from Vertically Merged Cells (i.e. only 1 column), which does not use VBA

First, the caveat - this skips over all Blank cells in a Column, to find the first cell with data on-or-above the same row as your Target Cell. As such, it will not pull 'blank' data, and does not care whether or not the cells are actually merged.

=INDEX($A$1:$A1, SUMPRODUCT(MAX(ROW($A$1:$A1)*--(LEN($A$1:$A1)>0))) ,1)

$A1 is your target cell. $A$1:$A1 will be the cells in-or-above the current row from Column A. This will expand as you drag the formula down (i.e. on Row 9 it will be $A$1:$A9)

The SUMPRODUCT will find the MAX Row where there is data (LEN($A1)>0).The INDEX then uses this to return the Value

This in an Array Formula, so you will need to use Ctrl+Shift+Enter after you type the formula in.

If there is no data in or above, then the formula will reference the top cell, and return 0 (See this article from Microsoft). We can return a blank ("") by using a CountA to see if there is any data:

=IF(COUNTA($A$1:$A1)<1, "", INDEX($A$1:$A1, SUMPRODUCT(MAX(ROW($A$1:$A1)*--(LEN($A$1:$A1)>0))),1))