Recently a thread at the Eng-Tips forum prompted me to look for help on the Go To-Special function in Excel. Pressing F5-Alt S, then clicking the help icon in the dialog box brought up:
It seems that the offline help AI can’t work out that if you click the Help icon in the Go To-Special dialog, that is probably what you want help with. If you give it a little hint, it gets the message:
Never mind that I had just come from the Go To dialog, so I must know how to find it, I’ll follow the link to the help. Here it is in full:
That’s it, the full extent of the offline help: the Find and Select function lets you find and select things. It doesn’t even mention using the F5 key, let alone give any details on the “Special” options.
Using the online help works better. It takes you straight to help on using Go To Special, and it gives a reasonable amount of detail (although still no mention of the F5 key).
Now I know that Microsoft would really, really like us to use their on line help, but dumbing down the off line help to the point of making it totally useless really isn’t the way to go about it. There are actually good reasons for using help off line:
- It’s quicker
- Many people still have slow and/or unreliable connections
- Many people like to work when travelling, or otherwise not connected to the Internet
One of the things that made the Lotus 123 spreadsheet so popular in the early days of personal computing was that it gave near instant helpful and relevant help at the touch of the F1 key. Now more than 30 years later, in spite of processing speeds and available storage increasing by factors of thousands, the help available in Excel doesn’t come close to matching what we had then.
OK rant over, let’s get back to what we can do with Go To-Special. The discussion linked above asked how we could use VBA to fill a table containing blanks using the last entry above the blanks as the text, and filling down to the next non-blank cell. Left to myself I would have looped through the table cell by cell in VBA, which would work OK, but there is a much simpler way (provided by the original poster in the discussion):
Select the range to be filled, then open the Go To-Special dialog (with F5 Alt-S), and select blanks (or press k):
All the blank cells will be selected, and the cursor will move to the first blank cell. Type “=” and select the cell immediately above:
then press Ctrl-Enter:
The table is instantly filled as required. The remaining step is to convert all the generated formulas to text, which can be done quickly by selecting the entire table, press Ctrl-C, then Paste-Special as values.
A few lines of VBA will do all this automatically:
Sub FillInBlanks() 'select the range you want to fill in and generate formulas: Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=+R[-1]C" ' Convert to text: With Selection .Copy .PasteSpecial xlPasteValues End With End Sub
For more on using Go To-Special see these articles at Chandoo’s Blog:
Newton Excel Bach, not (just) an Excel Blog 
Doug, it appears that there’s some kind of bug related to Goto Special Blanks, as per my post at http://dailydoseofexcel.com/archives/2015/02/10/go-to-special-blanks-no-longer-my-go-to-guy/.
The bug: It’s incredibly slow on say a full column of data, unless you call it via VBA.
I’ll be interested to see if you find the same. 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, and cilck OK.
On my PC, that takes just under a minute. And all the while you can see cell references appearing in the Name Box. But calling it via Range(“A:A”).SpecialCells(xlCellTypeBlanks).Select takes just about one second. Weird.
LikeLike
Hi Jeff. It took about 5 seconds for me, in an otherwise blank workbook. The cell addresses did show in the name box. That was with XL 2013 on a newish Toshiba lap top.
LikeLike
What kind of time do you get when you type this in the immediate window?
Range(“A:A”).SpecialCells(xlCellTypeBlanks).Select
LikeLike
jeff – I tried this in a new sheet in a small existing workbook.
With F5-special the time increased to about 7 seconds.
Using the immediate window it took less than a second.
In a sub with some Timer calls wrapped around it took 0.4 seconds the first time, reducing to 0.1 seconds thereafter.
Sub Timeblanks()
[B1] = Timer
Range(“A:A”).SpecialCells(xlCellTypeBlanks).Select
[B2] = Timer
End Sub
Performance was much the same with one blank near the top or with a couple of extra blanks added near the bottom.
Now here’s something strange. I changed the range to “A:C” and the run time increased to 17 seconds. I then moved the timer range to column D and the time went up to 23 seconds. I had a formula in D3 (=D2-D1), but that wasn’t causing the problem. The time for just column A stays at 0.1 seconds.
If I make Column A nearly all blanks the time goes up to 0.3 seconds.
For “A:B” I get 12 seconds
I just tried it with “A:D” (and timer cells in Column E). Still 23 seconds
Very strange.
LikeLike
More timer results:
Reducing the number of rows in the range reduces the execution time roughly in proportion to the number of rows:
A1:A100000: 0.03 seconds
A1:B100000: 1.2 seconds
A1:D100000: 2.2 seconds
So going from one column to two increases the time/cell by a factor of about 20.
LikeLike
I’m getting the same results as you. A:B takes less than a second. A:C takes 30.
LikeLike
The problem seems to be to do with the used range.
So here’s some code I’m running:
Sub test()
Dim TimeTaken As Date
Dim rng As Range
TimeTaken = Now()
Set rng = Range("A:B")
rng.SpecialCells(xlCellTypeBlanks).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 "Blanks: " & Selection.Cells.Count & vbNewLine & vbNewLine
End Sub
If I start with a blank sheet, then if I put some text in A2, here’s what I get:
UsedRange: $A$2
Range Searched: $A:$B
Time Taken: 00:00:00 seconds.
Blanks: 1
If I then add some text to A1048576, then I get this:
UsedRange: $A$2:$A$1048576
Range Searched: $A:$B
Time Taken: 00:00:04 seconds.
Blanks: 1048574
…which isn’t too bad: 4 seconds to identify 1048576 blanks.
But when I add some text to B2, I get this:
UsedRange: $A$2:$B$1048576
Range Searched: $A:$B
Time Taken: 00:03:38 seconds.
Blanks: 2097149
…which is pretty strange. 4 seconds to do the first million, three and a half minutes to do the next million.
LikeLike
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’m going to see how long it takes for 1 million cells, using all available columns.
BTW, I came across a similar problem when transferring data to Python. See: https://newtonexcelbach.wordpress.com/2014/06/08/data-transfer-to-python-update/
LikeLike
Good deduction, Sherlock. I think that’s it too, on reflection. Will update my DDOE post accordingly.
LikeLike
With a range A1:XFD64 (exactly 1048576 cells) it takes 0.125 seconds, A1:XFD128 too 0.23 seconds, and A1:XFD256 took 0.45 seconds 🙂
How we can use that to speed things up with very long columns, I’m not sure. Other than working column by column.
LikeLike
Correction, the problem seems to be related to the number of columns with Data. Any more than one, and you’ve got yourself a long wait. I’ve written this up as a post at http://dailydoseofexcel.com/archives/2015/08/25/warning-special-cells-slows-to-a-crawl-across-multiple-columns/
LikeLike
Jeff – I would say it is actually the number of rows that is the problem. One column works the same as one row, but for two or more columns each row is treated as a separate array, and that’s where all the overhead is occurring.
LikeLike
Pingback: Daily Dose of Excel » Blog Archive » Warning: Special Cells slows to a crawl across multiple columns