Clever Geek Handbook

Excel formula for the number of merged cells?

Is there a formula in Microsoft Excel that refers to a cell as an argument and returns the number of cells concatenated into one? For example, if I concatenate 3 cells, the formula returns 3 for that cell; if I concatenate 2 × 4 cells, the formula returns 8 and so on ...

+3


source to share


3 answers


Consider this little 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!

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

  • ALT-F11 opens VBE window
  • ALT-I ALT-M opens a new module
  • paste content and close VBE window

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

To remove the UDF:

  • bring up the VBE window as above
  • clear code
  • close VBE window

To use 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 UDF features:

https://web.archive.org/web/www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this!

+3


source


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



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


source


Using the offset function can help in a situation where you have data in a column to the left (or below), this formula counts the number of filled cells at a given distance from the merged cells.

Try this = COUNTA (OFFSET ('[merged range of cells], 0, -1))

-1 moves the counter one column to the left if there was data below the final it would be 1.0)

if there are only cells of spaces around, try the formula

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

if there is a mixture of empty and filled cells add formulas together

-1


source






More articles:


All Articles