VBA Remove format from empty cells in Excel (while ignoring the merged cells)

Question

I came across this link in the forum VBA Remove format from empty cells in Excel

However, I would like to clear the formatting of empty cells, ignoring the merged cells.

Answer 1

This should do the trick. You will need to make two updates here:

1. Update the sheet name in code to the sheet of interest
2. Update the range in code to the relevant range you want this code to work on (for testing purposes, the range is currently set to ws.Range("A1:A30"))

The macro will loop trough each cell in the provided range (which you need to update per #2) and will check to see if the cell is both empty AND unmerged. If the cell meets this criteria, we will add it to a range variable (Format_Range) and continue the loop. Once the loop is complete, clear the formatting of the variable range all at once.

---

Sub Custom_Format()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")        'Update Sheet Name

Dim Target As Range, Format_Range As Range

For Each Target In ws.Range("A1:A30")                              'Update Range to run macro on
    If Target.MergeCells = False And Target = "" Then
        If Not Format_Range Is Nothing Then
            Set Format_Range = Union(Target, Format_Range)
        Else
            Set Format_Range = Target
        End If
    End If
Next Target

'Make sure the range is not empty before removing format
If Not Format_Range is Nothing Then Format_Range.ClearFormats

End Sub

---

Sample Output

Here is a before (Column A) and after (Column B) photo when using the macro. Note that all unmerged cells had the formatting stripped from them when they were blank while merged cells kept their formatting regardless.

Answer 2

you could use SpecialCells property of Range object and get all blank cells on which testing if they are part of a merged area:

Dim cel As Range
With ActiveSheet ' <- change this to your actual sheet reference of interest
    With .UsedRange ' <- change this to your actual range reference of interest
        For Each cel In .SpecialCells(XlCellType.xlCellTypeBlanks).Cells
            If Not cel.MergeCells Then Debug.Print cel.ClearFormats
        Next
    End With
End With