Haphazardly Posted Excel Information and Other Stuff

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.

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:

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:

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

…it tells you there are none:

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

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?

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)

@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…

@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?

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.