An Excel formula for number of merged cells?

Question

Is there a formula in Microsoft Excel that takes a reference to a cell as argument and returns the number of cells merged into one? For example, if I merged 3 cells, the formula returns 3 for that cell; if I merged 2 × 4 cells area, the formula returns 8, and so on…

Answer 1

Consider this tiny UDF (User Defined Function):

Public Function MergeSize(r As Range) As Long
    MergeSize = r(1).MergeArea.Cells.Count
End Function

If the UDF returns 1, the cell stands alone!

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=MergeSize(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Answer 2

Excel does not have such a function built-in but it would be easy enough to write one.

Function mergedcells(rng As Range) As Integer
    mergedcells = rng.MergeArea.Cells.Count
End Function

Answer 3

Using the offset function can help in a situation if you have data in the column to the left (or below) the this formula counts the number of populated cells a set distance away from the merged cells.

Try the following =COUNTA(OFFSET('[merged cell range],0,-1))

the -1 is moving the count to one column to the left if there was data was below the ending would be ,1,0)

if there are only blanks cells around then try the formula

=COUNTBLANK(OFFSET('[merged cell range],0,-1))

if there is mix of empty and populated cells add the formulas together