Further to my previous post on Goto Special/Blanks, it looks like there is a pretty serious problem with the SpecialCells method if you use it on more than one column.
That’s because it’s blindingly fast at finding things in one column, but painfully slow in finding things in any other columns. Which is a problem, because anyone who’s anyone knows that using SpecialCells is supposed to be much more efficient than looping through a big range. Well, Mr and Mrs Anyone may want to reconsider using the loop on multiple columns. Or rather, they might want to consider looping through the columns, and use SpecialCells on each one individually. Best of both worlds.
So how fast is it on one column? And how slow on multiple columns? Let’s find out.
Here’s your test code.
Sub TestingTestingIsThisThingOn()
Dim TimeTaken As Date
Dim rng As Range
Dim Jeff As AboveAverage
TimeTaken = Now()
Set rng = Range(“A:B”)
rng.SpecialCells(xlCellTypeConstants, xlTextValues).Select
TimeTaken = Now() – TimeTaken
Debug.Print “UsedRange:” & vbTab & ActiveSheet.UsedRange.Address
Debug.Print “Range Searched:” & vbTab & rng.Address
Debug.Print “Time Taken:” & vbTab & Format(TimeTaken, “HH:MM:SS”) & ” seconds.”
Debug.Print “Cells: ” & Selection.Cells.Count & vbNewLine & vbNewLine
End Sub
Fill A:A with some text, such as “Blindingly Fast”, and run that sub. Here’s what I get:
UsedRange: $A:$A
Range Searched: $A:$B
Time Taken: 00:00:00 seconds.
Cells: 1048576
Now fill B:B with “Painfully Slow”, and run it again. Here’s what I get:
UsedRange: $A:$B
Range Searched: $A:$B
Time Taken: 00:02:02 seconds.
Cells: 2097152
What the…? So it took a blindingly fast 1 second to process one column, and a painfully slow three minutes to process two? That’s about as linear as my life story before I met and married Excel 2000. (My first wife. I still see her round from time to time in the forums. She hasn’t aged gracefully.)
So either there’s something SpecialCells doesn’t like about the number of columns, or there’s something it doesn’t like about the number 1048576. Let’s find out.
If you delete rows 524288 down, then you’re left with 2 columns of text totaling 1048576 cells. Here’s what I get if I run the code on that:
UsedRange: $A$1:$B$524287
Range Searched: $A:$B
Time Taken: 00:00:57 seconds.
Cells: 1048574
It took one minute to process this, as opposed to the one second it took to process the same amount of cells in one column. So it seems that it’s the multiple columns aspect that is causing the issue, and that the extra time incurred is dependent on the number of cells in that 2nd column. In fact, if I fill those blank cells in column A with our text (while leaving half of Column B with blanks) and rerun the code, then it doesn’t take Excel any longer to process those extra half million cells:
UsedRange: $A:$B
Range Searched: $A:$B
Time Taken: 00:00:52 seconds.
Cells: 1572863
So it seems the issue is in how Excel is handling the union of all those cells it identified in the first column with any cells it identifies in the second. And given how slow it is, I wouldn’t be surprised if it’s unioning those cells one at a time.
The same thing goes for blanks, by the way. This turned up in the comments of Doug Jenkin’s latest post.
Moral of the story: Do NOT use SpecialCells on more than one column at a time.
Question: How are you supposed to bring this stuff to MS’s attention, apart from posting it somewhere conspicuous like here?
—Update—
Over in the comments of his blog, Doug Jenkins hypothesizes:
A possibility is that a single column is treated as being a single array with 1 million values, but two columns are treated as being 1 million arrays, each with 2 values. When the operation is done on the spreadsheet with F5-Special, the name box shows that it works row by row, rather than all of Col A then all of Col B.
I think he’s right. And also after further testing, I realize that those times are down to how long Excel took to search all these arrays, and not to do with how long it took Excel to union the results. Because there weren’t that many results to union in my simple example, so it took practically no time at all.
But if you set up the data like so:
…then you see that the actual unioning takes a heck of a lot longer than the searching. For instance, as per above it took nearly two minutes for SpecialCells to search 1 million rows across two columns and identify a handful of blank areas. Whereas from the below table, you can see that with the greatly increased number of blanks that it’s got to union, it took over two minutes to union just 45,000 cells in one column.
Test 1 – 0 Seconds
Test 2 – 15 Seconds
Wow, that probably explains why my conditional format formula export using your code from CF Redux is SO slow!
Takes almost 2 hours to go through
80 columns,4000 rows and returns about 200 formulasNow, can we fix?