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.
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.
The no cells were found error is because you selected cells outise of the usedrange.
Other than that, goto special blanks takes a bit over a second on my system.
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?
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…
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).SelectI 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)
far less than seconds:
Sub M_snb()With Range("A1:A1020000")
.AutoFilter 1, "="
.SpecialCells(12).Select
.AutoFilter
End With
End Sub
@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.
On a related note, at https://fastexcel.wordpress.com/2011/05/25/writing-efficient-vba-udfs-part-1/#comment-2040 Charles says
Hi Jeff,
Excel 2010 32 bit on Win7 64 bit running on a Dell Precision M4500 (4 yrs old) with an core i7 (8 processor cores) and 16 gb of RAM.
Yes I entered one constant in one cell.
I meant all cells but one :-)
Cool. Is the Constants option significantly faster?