Filling Blanks with Go To-Special (and local help rant)

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
.PasteSpecial xlPasteValues
End With
End Sub

For more on using Go To-Special see these articles at Chandoo’s Blog:

This entry was posted in Excel, VBA and tagged , , , , , , . Bookmark the permalink.

13 Responses to Filling Blanks with Go To-Special (and local help rant)

  1. jeffrey Weir says:

    Doug, it appears that there’s some kind of bug related to Goto Special Blanks, as per my post at
    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.


    • dougaj4 says:

      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.


      • jeffrey Weir says:

        What kind of time do you get when you type this in the immediate window?


      • dougaj4 says:

        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
        [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.


    • dougaj4 says:

      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.


  2. jeffrey Weir says:

    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")

    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.


  3. Pingback: Daily Dose of Excel » Blog Archive » Warning: Special Cells slows to a crawl across multiple columns

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s