VBA - remove formatting from many blank cells

[JustDude]

Hi everyone,

I have data in over 200 000 rows, accross a couple columns (A:I). Some cells are filled in, some are blank - but all have certain formatting.
Now, I want to clear the formatting from blank cells only, starting from second row.
I need to do it the fastest way possible, but cannot sort the data. So I wrote this code:

Sub test()

ActiveSheet.Range("A2:I" & ActiveSheet.UsedRange.Rows.Count).SpecialCells(xlCellTypeBlanks).ClearFormats

End Sub

Unfortunately, the code doesn't work as expected - it removes formatting from ALL cells, instead of blank cells only. And it takes over 2 minutes to complete that (way too long!)
Can you help me out with that? What would be the best (fastest!) way to do that correctly?

Thank you very much in advance.

 

[jim may]

What can you do with this code?

Sub Foo()
With ActiveSheet.UsedRange
    .SpecialCells(xlCellTypeBlanks).ClearFormats
End With
End Sub

 

[Michael M]

Don't need With / End with

Sub Foo()
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).ClearFormats
End Sub

 

[JLGWhiz]

It is really odd that it works on everything except the font. If I set the used range font to bold and run the macro, it removes any colors and borders but only the first empty cell changes the font to normal. The rest will revert to bold.

 

[JustDude]

What can you do with this code?

Sub Foo()
With ActiveSheet.UsedRange
    .SpecialCells(xlCellTypeBlanks).ClearFormats
End With
End Sub

Don't need With / End with

Sub Foo()
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).ClearFormats
End Sub

Jim, Michael,
Thank you, but this still doesn't work
Same issues, it removes formatting from ALL of the cells, instead of blanks only - which is what I need. And it takes 2 minutes to complete that.

Is there any way to do that, as I described in my first post?

Thank you in advance.

 

[jim may]

With your subject sheet the activesheet go to VBE window and in the immediate window enter:

? Activesheet.Usedrange.address (and press enter)
What do you get? Is it covering the desired worksheet area?

While still in the immediate window now enter:
Activesheet.Usedrange.specialcells(xlcelltypeBlanks).select (and press enter) Note the EXCLUSION of the "?" at the beginning of the code.

Look NOW at your activesheet -- ONLY your BLANK CELLS SHOULD BE SELECTED? Is this the case??

 

[JLGWhiz]

Jim, Michael,
Thank you, but this still doesn't work
Same issues, it removes formatting from ALL of the cells, instead of blanks only - which is what I need. And it takes 2 minutes to complete that.

Is there any way to do that, as I described in my first post?

Thank you in advance.

Try adding this line before the clearformats line:

Application.Calculation = xlCalculationManual

and this one after the clearformats line:

Application.Calculation = xlCalculationAutomatic

That should speed up the process. But still confounded by the response to the SpecialCells method.

 

[JustDude]

With your subject sheet the activesheet go to VBE window and in the immediate window enter:

? Activesheet.Usedrange.address (and press enter)
What do you get? Is it covering the desired worksheet area?

It covers the used range area, and I get: $A$1:$I$208274

While still in the immediate window now enter:
Activesheet.Usedrange.specialcells(xlcelltypeBlanks).select (and press enter) Note the EXCLUSION of the "?" at the beginning of the code.
Look NOW at your activesheet -- ONLY your BLANK CELLS SHOULD BE SELECTED? Is this the case??

No, once I type: Activesheet.Usedrange.specialcells(xlcelltypeBlanks).select and press Enter, the worksheet "freezes" for about 2 minutes, and after that time it shows ALL cells selected, not the blank cells.

It's a huge file, see for yourself: Zippyshare.com

 

[Michael M]

And you are trying to clear over a million cells.
I think you may have to do a loop through rather than a mass edit !!

 

[JustDude]

And you are trying to clear over a million cells.
I think you may have to do a loop through rather than a mass edit !!

Michael,
But doing a loop through such a range takes much longer than 2 minutes!
Long time is just a side issue. The biggest issue here is that .SpecialCells(xlcellTypeBlanks) doesn't do its job - it removes formatting from ALL cells (instead of blanks only), as expected.