VBA – ClearFormats – Remove Formatting For Each Cell In Range
In this Article
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.
|
1 2 3 |
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:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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!
