Excel Hacks Ebook [Electronic resources]

Hack 63 Fill All Blank Cells in a List

Often, many people will leave a blank cell if the data for that cell is the same as the cell above it. Visually this makes lists easy to read, but structurally it is not a good idea. With the hacks in this section, you can fill all blank cells in a list quickly and easily.

Many of Excel's functions are designed to be used on lists. For most of these functions to work correctly, lists should not contain any blank cells, and column headings should be formatted differently from the data in the list.

When setting up data in Excel, it is good practice to ensure that all cells within a list are occupied. However, many lists are set up in a similar manner to the one shown in Figure 6-3.

Whereas prices are repeated in the Cost column, the types of fruits in the Fruits column are not. As discussed at the beginning of Chapter 1, this will create many problems when using features such as Subtotals and PivotTables. In most cases, Excel expects your related data will be set up in a continuous list or table, with no blank cells.

There are two ways in which you can fill blank cells in a list: with a formula and with a macro.

Method 1: Filling Blanks Via a Formula

Say you have a list of entries in column A, similar to the previous example, and within the list you have many blank cells. Here is a quick and easy way to fill those blanks with the values of the cells above them.

Select all the data in column A, select Edit Go To... (Ctrl-G), and then click Special. Check the Blanks option and click OK. At this point, you have selected only the empty cells within the list. Press = (the equals key), then the up arrow. Holding down the Ctrl key, press Enter.

You can quickly convert formulas to values only by selecting column A in its entirety. Select Edit Copy (Ctrl-C), then select Edit Paste Special..., check the Values checkbox, and then click OK.

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 Tools Macro Visual Basic Editor (Alt/Option-F11), then select Insert 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 select Tools Macro Macros (Alt/Option-F8), select Fill Blanks, and click Run, or use Options to assign a shortcut key.