VBA – ClearFormats – Remove Formatting For Each Cell In Range

This tutorial will demonstrate how to clear the formatting of a cell or entire range of cells.

You can clear cell formatting by using the ClearFormats method of the Range Object.

Note: Instead, if you’d like to clear everything about the cell use .Clear instead.

Clear Formats From a Range of Cells

This will clear Cell Formatting for Range A1:C1.

Sub ClearFormats()
      Range("A1:C1").ClearFormats
End Sub

If you’d like to clear the formatting of an entire worksheet, use the Cells Object instead:

Sub ClearSheetFormats()
      Cells.ClearFormats
End Sub

If you are working with an extremely large range in a large Workbook, Excel could potentially freeze. you could also loop through each cell to prevent Excel from freezing.

Loop through Cells and Clear Formats

The following example will eliminate the formatting for each cell in a range using a loop:

Public Sub RemoveFormatting()
Dim c As Range

For Each c In Range("A:A")
     c.ClearFormats
Next c

End Sub