Newton Excel Bach, not (just) an Excel Blog

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:

• http://chandoo.org/wp/2011/10/17/fill-blank-cells-in-a-table/
• http://chandoo.org/wp/2012/03/12/go-to-special/
• http://chandoo.org/wp/2011/02/14/compare-data-row-differences/