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.

 

[Peter_SSs]

Michael,
But doing a loop through such a range takes much longer than 2 minutes!

JustDude
Depends on what the loop is doing I'd say.

Firstly though, if you are using Excel 2007 then you will be exceeding the SpecialCells limit as described here.

But let's assume you are using Excel 2010 or later where that limit is increased dramatically. Whilst you won't be exceeding the limit any more, you are asking an awful lot of the SpecialCells feature and your machines resources, a situation I confirm finding on my machine using Excel 2010.

I set up some sample data of 200,000 rows and 9 columns where about half the cells were blank. My machine is probably less powerful than yours and it took much longer than 2 minutes & I gave up waiting after about 4 minutes.

I suggest that you have a play around with this code, altering the BlockSize constant which determines how many rows to process at a time. It will depend a bit on what proportion of blank cells you are likely to have and just how randomly they are arranged with the data.
With my data, I found a value of about 100 gave a pretty good processing time of less than 4 seconds for the 200,000 rows.
Of course the asterisked rows are only there for testing & can be removed once you are happy.

Sub test()
  Dim lr As Long, i As Long
  Dim t As Single '*******************
  t = Timer '*******************

  Const BlockSize As Long = 100 '<- Try different values here
  
  lr = Columns("A:I").Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
      SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  Application.ScreenUpdating = False
  On Error Resume Next
  For i = 2 To lr Step BlockSize
    Range("A" & i).Resize(BlockSize, 9).SpecialCells(xlCellTypeBlanks).ClearFormats
  Next i
  On Error GoTo 0
  Application.ScreenUpdating = True
  MsgBox "Code took " & Format(Timer - t, "0.000 secs") '*******************
End Sub

 

[JustDude]

JustDude
Depends on what the loop is doing I'd say.

Firstly though, if you are using Excel 2007 then you will be exceeding the SpecialCells limit as described here.

Hi Peter,
That is true - I'm using Excel 2007, so that link you mentioned would explain why SpecialCells is applied to all cells instead of blanks only.

But let's assume you are using Excel 2010 or later where that limit is increased dramatically. Whilst you won't be exceeding the limit any more, you are asking an awful lot of the SpecialCells feature and your machines resources, a situation I confirm finding on my machine using Excel 2010.

I set up some sample data of 200,000 rows and 9 columns where about half the cells were blank. My machine is probably less powerful than yours and it took much longer than 2 minutes & I gave up waiting after about 4 minutes.

I suggest that you have a play around with this code, altering the BlockSize constant which determines how many rows to process at a time. It will depend a bit on what proportion of blank cells you are likely to have and just how randomly they are arranged with the data.
With my data, I found a value of about 100 gave a pretty good processing time of less than 4 seconds for the 200,000 rows.
Of course the asterisked rows are only there for testing & can be removed once you are happy.

Sub test()
  Dim lr As Long, i As Long
  Dim t As Single '*******************
  t = Timer '*******************

  Const BlockSize As Long = 100 '<- Try different values here
  
  lr = Columns("A:I").Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
      SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  Application.ScreenUpdating = False
  On Error Resume Next
  For i = 2 To lr Step BlockSize
    Range("A" & i).Resize(BlockSize, 9).SpecialCells(xlCellTypeBlanks).ClearFormats
  Next i
  On Error GoTo 0
  Application.ScreenUpdating = True
  MsgBox "Code took " & Format(Timer - t, "0.000 secs") '*******************
End Sub

Thank you very much for the code!
I will be testing it, and let you know about the results.

regards,
Matt (JustDude)

 

[JustDude]

I suggest that you have a play around with this code, altering the BlockSize constant which determines how many rows to process at a time. It will depend a bit on what proportion of blank cells you are likely to have and just how randomly they are arranged with the data.
With my data, I found a value of about 100 gave a pretty good processing time of less than 4 seconds for the 200,000 rows.
Of course the asterisked rows are only there for testing & can be removed once you are happy.

Sub test()
  Dim lr As Long, i As Long
  Dim t As Single '*******************
  t = Timer '*******************

  Const BlockSize As Long = 100 '<- Try different values here
  
  lr = Columns("A:I").Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
      SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  Application.ScreenUpdating = False
  On Error Resume Next
  For i = 2 To lr Step BlockSize
    Range("A" & i).Resize(BlockSize, 9).SpecialCells(xlCellTypeBlanks).ClearFormats
  Next i
  On Error GoTo 0
  Application.ScreenUpdating = True
  MsgBox "Code took " & Format(Timer - t, "0.000 secs") '*******************
End Sub

Hi Peter,
Your code works great!!
I have tested it with BlockSize=100, and having more than 208 000 rows to process in Excel 2007, it does the required job perfectly... within just 2 seconds!!
Great workaround for SpecialCells limitation in Excel 2007.

Many, many thanks, Peter!!!

regards,
Matt (JustDude)

 

[Peter_SSs]

Good news. Thanks for letting us know Matt.
(Looping wasn't so bad after all! )