Menu
Using VBA, I am trying to Clear all Formats all entire of the active Worksheet.
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
With ws
.ClearFormats
End With
But I am getting following error:
However using the same code with .UsedRange works fine as:
With .UsedRange
.ClearFormats
End With
Can you please let me know how fix this to format entire sheet!
628
To clear all contents, formats, and comments that are contained in the selected cells, click Clear All. To clear only the formats that are applied to the selected cells, click Clear Formats. To clear only the contents in the selected cells, leaving any formats and comments in place, click Clear Contents.
Excel VBA Clear Contents. ClearContents is a method in VBA used to delete or remove the values stored in the cells provided to it. This method makes the cell range empty. It is used with the range property to access the specified cell range.
To remove cell formatting in Excel, select the cells from which you want to remove all of the formatting. Then click the “Home” tab in the Ribbon. Then click the “Clear” button in the “Editing” button group. Finally, select the “Clear Formats” command from the drop-down menu that appears.
First, select the cells of your dataset and press ALT+F11 to open the VBA window. After that, press CTRL+G. It will open the Immediate Window. The code will clear the contents from your selected cells without deleting the formula.
ClearFormats is a method of a Range, wereas ActiveSheet is a Worksheet
You could use
ThisWorkbook.ActiveSheet.Cells.ClearFormats
(Cells is a range of all the cells on a worksheet)
70
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
