VBA: How to Clear Formatting from Cells

You can use the following methods to clear the formatting from cells in Excel by using VBA:

Method 1: Clear Formatting from Specific Cells

Sub ClearFormattingRange()
    Range("A2:A11").ClearFormats
End Sub

This particular macro will clear the formatting from all cells in the range A2:A11 in the currently active sheet.

Method 2: Clear Formatting from All Cells in Sheet

Sub ClearFormattingAll()
    Cells.ClearFormats
End Sub

This particular macro will clear the formatting from all cells in the currently active sheet.

The following example shows how to use each method in practice with the following sheet in Excel:

Example 1: Use VBA to Clear Formatting from Specific Cells

Suppose we would like to clear the formatting from all cells in the range A2:A11.

We can create the following macro to do so:

Sub ClearFormattingRange()
    Range("A2:A11").ClearFormats
End Sub

Once we run this macro, the formatting in all cells in the range A2:A11 will be cleared:

Notice that the italic font, red font color, and borders have all been removed from the cells in the range A2:A11.

All other cells in the sheet kept their formatting.

Suppose we would like to clear the formatting from all cells in the sheet.

We can create the following macro to do so:

Sub ClearFormattingAll()
    Cells.ClearFormats
End Sub

Once we run this macro, the formatting in all cells in the entire sheet will be cleared:

Notice that the formatting has been cleared from all cells in the entire sheet.

Note: You can find the complete documentation for the ClearFormats method in VBA here.

The following tutorials explain how to perform other common tasks in VBA: