VBA code to fill in blank cells running really slowly

[SimonR]

Hi,

I have a spreadsheet that originally contained a lot of merged cells in column H and the cells to the left of them were blank. This caused problems with the filter in column D even after I unmerged the whole of column H, so I began filling in the blanks in D with x's. This was really time-consuming because for some reason filtering column D to show only blanks (so I could fill the whole empty space with x's) didn't work, and these many sections with blanks alternated with those with text. So I tried this code to speed things up:

Sub fillblankcells()
Dim r As Integer
r = 11382
Do Until r = 33453
If Cells(r, 4) = "" Then
Cells(r, 4) = "x"
End If
r = r + 1
Loop

End Sub

However, after a couple of minutes and me stopping the code, the cells in column D have been filled only up to row 11667. Any idea what is wrong with my code?

 

[Joe4]

Loops are notoriously slow in VBA, especially if you are updating cells.

You can quickly and easily fill all the blank cells in a range with a value without using VBA by using this method here: Microsoft Excel: How to Quickly Fill Blank Cells in a Worksheet

If you need VBA code, you can use VBA to perform the steps shown there.

 

[Joe4]

Or you can just use this one line of code:

Sub fillblankcells()
    Range("D11382:D33453").SpecialCells(xlCellTypeBlanks).Value = "x"
End Sub

 

[SimonR]

Loops are notoriously slow in VBA, especially if you are updating cells.

You can quickly and easily fill all the blank cells in a range with a value without using VBA by using this method here: Microsoft Excel: How to Quickly Fill Blank Cells in a Worksheet

If you need VBA code, you can use VBA to perform the steps shown there.

Thanks, I didn't know about that although I'm familar with the Go To Special function.

So why can blank cells cause problems with filters (not the first time I've experienced this problem)? Is the rule that for everything to be included in a list for filtering, if the list contains blanks, there needs to be at least one column elsewhere with continuous text?

 

[Joe4]

So why can blank cells cause problems with filters (not the first time I've experienced this problem)? Is the rule that for everything to be included in a list for filtering, if the list contains blanks, there needs to be at least one column elsewhere with continuous text?

I am not sure what you mean. Could you show us an example of your data and exactly how you are filtering?
Also, which version of Excel are you using here, 2010 or 365 (you have selected both)?

Lastly, not sure if you saw my last post with the one-line VBA code that would fix it.

 

[SimonR]

I use 365 for work and both 2010 and 365 at home (leaving aside working at home). I probably ought to edit my account details if necessary before I submit a question so people are clear what version I'm using when I have a problem (though it's usually 365 these days).

I played around with various sets of data and I discovered the answer to my own question - you can't properly filter sets of data if you have at least one blank row in them - that was what caused the problem. I did see your second post before I replied and the code did the trick, thanks.

 

[Joe4]

You are welcome.
Glad you got it working!