Method 2: Filling Blanks via a Macro
If you will be filling in blank cells frequently, you should consider handling this via a macro.
The following macro makes this a breeze. To use it, select Alt/Option-F11, then select Insert →0 Module and enter the following code:
Sub FillBlanks( )
Dim rRange1 As Range, rRange2 As Range
Dim lReply As Integer
If Selection.Cells.Count = 1 Then
MsgBox "You must select your list and include the blank cells", _
vbInformation, "OzGrid.com"
Exit Sub
ElseIf Selection.Columns.Count > 1 Then
MsgBox "You must select only one column", _
vbInformation, "OzGrid.com"
Exit Sub
End If
Set rRange1 = Selection
On Error Resume Next
Set rRange2 = rRange1.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rRange2 Is Nothing Then
MsgBox "No blank cells Found", _
vbInformation, "OzGrid.com"
Exit Sub
End If
rRange2.FormulaR1C1 = "=R[-1]C"
lReply = MsgBox("Convert to Values", vbYesNo + vbQuestion, "OzGrid.com")
If lReply = vbYes Then rRange1 = rRange1.Value
End Sub
After entering this code, close the window to get back to Excel, and then save your workbook. Now go to the Developer tab and select Code → Macros or Alt/Option-F8 (pre-2007, Tools → Macro → Macros → FillBlanks → Run) or use Options to assign a shortcut key.
