Excel ClearContents or Cell Set to = “ “

Question

I’m trying to have a macro button to clear contents of a range of cells, and upon using the code below , It clears the contents of merged and unmerged cells. Is there a way to not clear the contents if the cell is merged in the range of cells?

Sub Delete1()

Range("$E$5:$AI$5") = ""

End Sub

Answer 1

Check if the MergeArea.Address matches the address of a cell reference or MergeArea.Cells.Count to determine whether it falls within a merged cell area.

Sub Delete1()
    Dim c As Range
    For Each c In Range("$E$5:$AI$5")
        If c.MergeArea.Address = c.Address Then
            If Len(c.Value)>0 Then c.Value = ""
        End If
    Next
End Sub

OR

Sub Delete1()
    Dim c As Range
    For Each c In Range("$E$5:$AI$5")
        If c.MergeArea.Cells.Count = 1 Then
            If Len(c.Value)>0 Then c.Value = ""
        End If
    Next
End Sub