VBA – ClearFormats – Remove Formatting For Each Cell In Range

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on August 11, 2022

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

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

 

Learn More!

 

vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples