Go To Special Blanks no longer my Go To guy…

So I’ve always used Excel’s Go To Special and VBA’s SpecialCells method to select things like formulas, constants, blanks etc from large ranges because I was under the impression that this was efficient. Is is, unless you’re using it to find blanks, in which case it’s a dog.

Try this: Select column A:A, and use Ctrl + Enter to enter say the number 1 into the whole column. Now, delete one of the cells so there’s a blank, push F5 to bring up Goto Special, select the Blanks option, click OK, and go put the kettle on.

Goto Special Blanks

It took about 54 seconds on my PC. Admittedly my PC has been running slow of late, but that’s ridiculous.

Now try the Constants option:
Goto Special Constants

Just over a second.

And in case you think the number of blanks (1) vs the number of constants(1048574) is the culprit, you’re wrong. This takes just as long:
Goto Special Blanks2

Ironically – perhaps moronically – if you use the Go To Special>Blanks option on a range outside of the used range:
Goto Special Blanks3

…it tells you there are none:
No Cells were found

…which is about as helpful as that “Was this information helpful?” message.

So from now on, instead of Go To Special > Blanks I’ll be using Chip Pearson’s FindAll function. You?

I’m using Excel 365 on Windows 8. Anyone NOT get the same behavior on different flavors? Googling vba specialcells xlCellTypeBlanks slow brings up heaps of hits. Quickly.

13 thoughts on “Go To Special Blanks no longer my Go To guy…

  1. Yeah, I get the ‘Outside of used range’ thing. I still think it should select blanks, because those cells are blank. I’m primarily thinking about when it’s called via VBA to do something based on some other parameter, and happens to point to a range partially outside of the used range. Not a biggie.

    One second? Well that’s better. What XL Version and OS are you running, Jan Karel? And I take it you’ve entered constants into every cell but one as per my example?

  2. Same behaviour on Excel 2010 under Windows 7,in that finding blanks is noticeably slower than finding constants, regardless of how many of them there are.

    However, we’re talking about 3 seconds versus virtually instantaneous. If a person could be bothered, they could time this more accurately using VBA and see if the ratio of time taken is the same…

  3. It does not take 54 seconds on my system, but it does take a couple of seconds.

    What strikes me is that the following line of code takes only a fraction of a second!

    Selection.SpecialCells(xlCellTypeBlanks).Select

    I have created a userform that lets a user select all Blanks, Nonblanks, Formulas, Constants, Errors, or Text values with one click, which is working more efficient for me than the built-in Goto Special (from a GUI perspective, don't know about processing efficiency)

  4. far less than seconds:

    Sub M_snb()
    With Range("A1:A1020000")
    .AutoFilter 1, "="
    .SpecialCells(12).Select
    .AutoFilter
    End With
    End Sub

  5. @JLeno: Selection.SpecialCells(xlCellTypeBlanks) is simply the method used by Go To Special > Blanks.

    @snb: Good approach, although you’ll have to check if the first cell is a blank and deselect if not. How long did it take the Go To Special > Blanks route by comparison? Your approach takes two to three seconds on my PC – same as Chip’s FindAll function.

    On a friend’s machine, Go To Special > Blanks took 12 seconds. Using Find took about 0.3 seconds. On my other machine, it takes about 8 seconds. So Go To Special > Blanks and it’s VBA equivalent SomeRange.SpecialCells(xlCellTypeBlanks).Select is a dog for both of us compared to snb’s code or Chip’s FindAll function.

    I don’t know why Go To Special > Blanks takes so long on my main machine, but I get lots of screen flickering too. Mind you I don’t see it, because I’m off boiling the kettle…

  6. @Jeff: although i do agree with you conceptually, there is a huge performance difference on my machine between the Goto Special-> Blanks (5-6 seconds) and the VBA equivalent (0.1 – 0.2 seconds). Perhaps it’s just my machine though

    What I do note is that when I use the Goto Special method, i actually see the selected cell changing in the Name Box in the top-left of my Excel screen… This does not happen when I use the VBA equivalent. Could that make any performance impact?

  7. Hi JLeno. Good point re the name box…I’d not really noticed that. Quite possibly it could be the impact. Charles Williams could probably tell us…he’s highlighted some VBA refresh bugs before – maybe this is similar. Thanks for the comment.

Leave a Reply

Your email address will not be published. Required fields are marked *