How can I clear formatting from cells in VBA?

By /

The process of removing any formatting applied to cells in VBA (Visual Basic for Applications) can be achieved by using the ClearFormats method. This method clears all formatting, including font styles, borders, and cell fill colors, from the selected cells in a Microsoft Excel worksheet. It is a useful tool for individuals who wish to standardize the appearance of their data or start with a clean slate for formatting. By simply calling the ClearFormats method in VBA, all formatting will be removed, and the cells will revert to their default appearance. This process is straightforward and efficient, making it a popular choice for managing cell formatting in VBA.

VBA: 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").ClearFormatsEnd 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.ClearFormatsEnd 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").ClearFormatsEnd 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.

Example 2: Use VBA to Clear Formatting from All Cells in Sheet

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.ClearFormatsEnd 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 .

Additional Resources

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

x

Related terms:

  1. How do I apply conditional formatting to cells using VBA?
  2. How to Clear Filters in Excel Using VBA (With Example)
  3. How can I use conditional formatting in Google Sheets to highlight cells that are greater than or equal to a certain value?
  4. How can I ignore blank cells in conditional formatting in Excel?
  5. How can I apply conditional formatting in Excel to highlight cells that contain partial text?
  6. How can I apply conditional formatting in Excel to highlight cells that contain formulas?
  7. How to Apply Conditional Formatting to Blank Cells in Excel?
  8. How to apply conditional formatting if two cells are not equal
  9. Excel: How do you ignore blank cells in conditional formatting?
  10. How do I clear the contents of a cell in Excel without removing the formulas?