VBA Excel: Function not working on combined cells-CodePudding

I'm having problems with combined cells.

The following ranges are combined: G42:Q42, G43:Q43 and so on and so forth until G47:Q47

This code works fine when the cells aren´t combined as descripted above, but I can't get it to work when the cells are merged

For Each cell In ventas.Range("G42:G47")
   If (cell.Value = "") Then cell.ClearContents
Next

I've tried this but it's useless:

For Each cell In ventas.Range("G42:Q47")
   If (cell.Value = "") Then cell.ClearContents
Next

CodePudding user response：

Try this


Sub clearRange()
Dim c As Range

For Each c In ventas.Range("G42:Q47").Cells
    If c.MergeCells = True Then
        If c.MergeArea.Cells(1, 1) = "" Then c.MergeArea.ClearContents
    Else
        If c.Value = "" Then c.ClearContents
    End If
Next

End Sub

The code checks if the cell is part of a merge and then only works on the first cell. Working with merged cells can be very tricky.

CodePudding user response：

combined cells can also be addressed as a single cell.

Say I combine C3:G7 I can run

ActiveWorkbook.Sheets(1).Cells(3, 3).Value = "test"

and the cell will display"test" You can look up the name of the cell in the top left