Loop over a range of cells which contains merged cells

Question

I want to check if all cells with certain properties (a specific color for example) in a given range are not empty.

The problem is that inside that range, there are some merged cells, and those vary in dimensions (So, I can't just detect them and work around them systematically).

Is there anything like For each cell in Range([some range])

that would give me only one cell for a merged cell?

I tried it and it gives me the standard cells, which means that the merged cells are counted more than once. And the content of the cell is assigned only to the upper left corner cell (So, it detects empty cells in merged cells)

I also thought about the offset function, but I don't think it would work (For example here. Black cells are merged cells):

What have you tried so far? What do you expect to do with the code? Just check if cells in a range are merged? — BruceWayne

Wolfie Wolfie · Accepted Answer · 2017-08-10T16:44:59

You can use the below function. I have commented it to explain what is happening, but essentially:

Loop through all cells in a given range
Check if the cell is the first cell in its MergeArea. Note, the MergeArea is just the cell itself if it isn't merged.
Check if empty.
Print a debug statement if empty showing the address of the cell / its MergeArea.

Code:

Function EmptyTest(rng As Range) As Boolean
    EmptyTest = False ' Set to true if any cell in rng is empty
    Dim cell As Range
    For Each cell In rng
        ' Add your custom check here, e.g. for cell colour is black test do
        ' If cell.Interior.Color = RGB(0,0,0) Then 

        ' Check it is the first cell in MergeArea
        If cell.Address = cell.MergeArea.Cells(1).Address Then
            ' Check if it's empty
            If Len(cell.MergeArea.Cells(1).Value) = 0 Then
                EmptyTest = True
                ' Have the immediate window open to see debug statements
                Debug.Print "Range: " & cell.MergeArea.Address & " is empty."
            End If
        End If

        ' "End If" here if you added a custom check like the interior colour demo above.
    Next cell
End Function

Example:

In the VBA editor, call

EmptyTest Range("A1:G4")

Output in the immediate window*:

Range: $A$1:$B$4 is empty.
Range: $C$3 is empty.
Range: $G$4 is empty.

This function also returns True or False depending on whether any of the cells are empty.

*The immediate window can be opened in the VBA editor by pressing Ctrl + G.

Loop over a range of cells which contains merged cells

1 Answers