In Excel, you can combine all the existing formulas with the data that you have. However, there are some limitations for which we need to use Visual Basic for Applications (VBA). Such is the case with counting merged cells.
In the example below, we will show how to do it.
Count Merged Cells in Excel
For our example, we will use the sales results for January through March, and for different regions: North, East, West, and South:
As seen from the table, all the months, and three regions: East, West, and South are merged. To merge the cells in the first place, we have to choose the cells to be merged, then go to the Home tab >> Alignment and choose any option from the Merge & Center dropdown:
We already did this for our example. If we would want to count the number of cells in the range where merged cells are located (not including headers, we would usually try the following formula:
|
1 |
=COUNT(A2:A11,C2:C11) |
The result that we should have is 6, as the total number of merged areas is 6. When we type in the formula, the result will be 0, which is not true:
To count the merged cells, we need to use VBA. We will open it by clicking ALT + F11 on our keyboard. On the window that appears we will choose the Insert tab and then choose Module:
In the window that appears, we will insert the following formula:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Function CountMergedCells(pRng As Range) As Long Dim r As Range Dim total As Long Set j = CreateObject("Scripting.Dictionary") For Each r In pRng If r.MergeCells Then TempAddress = r.MergeArea.Address j(TempAddress) = "" End If Next CountMergedCells = j.Count End Function |
VBA has a built-in feature to create formulas. In our code, we did just that. Our formula has one parameter, which is range. We first declare two variables, r as range and total as long (a number). Then we set the j variable to create an object from the Scripting library.
For the most important part of the code, we input the For Each Loop in which we declare that for every instance in our range where cells are merged, we declare the TempAddress which will be stored and counted in variable j.
Then we define that the count of all the total merged cells is equal to our j variable.
Since this is not a standard Macro, but a formula, we cannot call it by clicking F5 on our keyboard. We will return to our Excel file, and simply call our formula by its name- CountMergedCells. The formula only has one parameter- range, and we will choose range A2:C11. Our formula (which we will insert in cell B15) will be:
|
1 |
=CountMergedCells(A2:C11) |
And our result will be 6:
This is the result we were aiming for since we’ve got January, February, March, East, West, and South merged in our range.