Removing formats from an entire worksheet using VBA

Question

Using VBA, I am trying to clear all formats of the entire active active sheet.

Dim ws As Worksheet Set ws = ThisWorkbook.ActiveSheet With ws .ClearFormats End With 

But I get the following error:

However, using the same code with .UsedRange works fine like:

 With .UsedRange .ClearFormats End With 

Could you tell me how to fix this in order to format the whole sheet?

Answer 1

ClearFormats is a Range method, areas ActiveSheet is Worksheet

you can use

 ThisWorkbook.ActiveSheet.Cells.ClearFormats 

( Cells - the range of all cells on a sheet)