Fill Blank Cells in Excel Column

Search Contextures Sites
Fill Blank Cells in Excel Column

Fill Blank Cells Manually
Fill Blank Cells in Excel 2010 / 2007
Fill Blank Cells in Excel 2003
Fill Blank Cells Programmatically
-- Fill Blank Cells Macro - Example 1
-- Fill Blank Cells Macro - Example 2
-- Fill Blank Cells Macro - Example 3
Download the Sample File

Fill Blank Cells Manually

Some worksheets contain cells that have been left blank, in order to make the headings and subheadings easier to read.

However, if you want to sort or filter the list, you need to fill in the blanks, by copying the value from the first filled cell above the blank.

The following technique makes it easy to fill in the blanks. There are instructions for Excel 2010 / 2007 and earlier versions.

Fill Blank Cells in Excel 2010 / 2007

Select the Empty Cells

To select the empty cells, use Excel's built in Go To Special feature:

Select columns A and B, that contain the blank cells.

On the Ribbon's Home tab, click Find & Select

Click Go To Special

In the Go To Special dialog box, click Blanks, then click OK

Create a Formula

The next step is to create a formula that will copy the value from the first heading above each blank cell.

With the blank cells selected, type an equal sign, to start the formula.

On the keyboard, press the up arrow. This enters a reference to the cell above – cell A2 in this example.

Press the Ctrl key and tap the Enter key, to enter the formula in all the selected cells.

Change Formulas to Values

Before you sort or filter the data, change the formulas to values. Otherwise, you'll end up with a mess.

Note: Do this carefully if other cells in the columns contain formulas. You don't want to accidentally change those formulas to values.

Select the entire columns where you filled in the blanks with formulas -- columns A:B in this example

On the Ribbon's Home tab, click Copy.

Click Copy

With the columns still selected, click the drop down arrow on the Paste command

Click Paste Values

The blank cells are now filled in with values, and you can safely sort or filter the list.

Fill Blank Cells in Excel 2003

View the steps for filling in blank cells in Excel 2003, in this short video tutorial. Written instructions are below the video.

Start by selecting the empty cells:

Select the cells in the column, starting in the row below the column heading.

Choose Edit | Go To

Click the Special button

Select Blanks, click OK

Enter the formula to copy the value:

Type an equal sign

Press the up arrow on the keyboard -- this will enter a reference to the cell above -- cell A2 in this example

Hold the Ctrl key and press Enter -- this enters the formula in all selected cells

Change the formulas to values:

In order to sort or filter the data, the formulas must be changed to values.

Select the entire column

Choose Edit | Copy

With the column still selected, choose Edit | Paste Special

Select Values, click OK

Note: Do this carefully if there are other cells in the range which contain formulas.

Fill Blank Cells Programmatically

If you frequently have to fill blank cells, you may prefer to use a macro. The following code examples will fill blank cells in the active column. Each example uses a different method to find the last row, and to fill the blank cells.

For more information on finding the last row, see Ron de Bruin's page: Find last row, column or last cell. Ron explains the advantages and disadvantages of each method.

Fill Blank Cells Macro - Example 1

The first example, from Dave Peterson, uses a formula to fill the cells, and pastes the results as values. The code uses the .SpecialCells(xlCellTypeLastCell) method to find the last row.
Sub FillColBlanks()
'by Dave Peterson  2004-01-06
'fill blank cells in column with value above
'http://www.contextures.com/xlDataEntry02.html
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long

Set wks = ActiveSheet
With wks
   col = activecell.column
   'or
   'col = .range("b1").column

   Set rng = .UsedRange  'try to reset the lastcell
   LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
   Set rng = Nothing
   On Error Resume Next
   Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
                  .Cells.SpecialCells(xlCellTypeBlanks)
   On Error GoTo 0

   If rng Is Nothing Then
       MsgBox "No blanks found"
       Exit Sub
   Else
       rng.FormulaR1C1 = "=R[-1]C"
   End If

   'replace formulas with values
   With .Cells(1, col).EntireColumn
       .Value = .Value
   End With

End With

End Sub       
Fill Blank Cells Macro - Example 2

In the following code, Rick Rothstein uses the .Find method to calculate the last row. Instead of using a formula to fill from above, each cell gets its value from the cell above the first cell of the Area that it's in, using the Offset property.
Sub FillColBlanks_Offset()
'by Rick Rothstein  2009-10-24
'fill blank cells in column with value above
'http://www.contextures.com/xlDataEntry02.html

  Dim Area As Range, LastRow As Long
  On Error Resume Next
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
               SearchDirection:=xlPrevious, _
               LookIn:=xlFormulas).Row
  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
               SpecialCells(xlCellTypeBlanks).Areas
    Area.Value = Area(1).Offset(-1).Value
  Next
End Sub
Fill Blank Cells Macro - Example 3

This example combines Dave Peterson's code (Example 1), with the special cells test from Ron de Bruin.

In Excel 2007, and earlier versions, there is a problem with special cells if there are more than 8192 different areas in the special cells range. This problem has been fixed in Excel 2010.

This code tries to count the areas, and if over the limit, it loops through the range in groups of 8000 rows.
Sub FillColBlanksSpecial()
'http://www.contextures.com/xlDataEntry02.html
'by Dave Peterson  2004-01-06
'fill blank cells in column with value above

'2010-10-12 incorporated Ron de Bruin's test for special cells limit
'http://www.rondebruin.nl/specialcells.htm

Dim wks As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim LastRow As Long
Dim col As Long
Dim lRows As Long
Dim lLimit As Long

Dim lCount As Long
On Error Resume Next
 
lRows = 2 'starting row
lLimit = 8000

Set wks = ActiveSheet
With wks
   col = ActiveCell.Column

   Set rng = .UsedRange  'try to reset the lastcell
   LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
   Set rng = Nothing
   
    lCount = .Columns(col).SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Count
    
    If lCount = 0 Then
        MsgBox "No blanks found in selected column"
        Exit Sub
    ElseIf lCount = .Columns(col).Cells.Count Then
        MsgBox "Over the Special Cells Limit" 'this line can be deleted
        Do While lRows < LastRow
            Set rng = .Range(.Cells(lRows, col), .Cells(lRows + lLimit, col)) _
                           .Cells.SpecialCells(xlCellTypeBlanks)
            rng.FormulaR1C1 = "=R[-1]C"
            lRows = lRows + lLimit
        Loop
    Else
        Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
                       .Cells.SpecialCells(xlCellTypeBlanks)
        rng.FormulaR1C1 = "=R[-1]C"
    End If

   'replace formulas with values
   With .Cells(1, col).EntireColumn
       .Value = .Value
   End With

End With

End Sub
Download the Sample File

To see the report with blank cells, and test the Fill Blanks macros, you can download the sample file. The file is zipped, and is in Excel 2010 / 2007 format, and contains macros.
1. Data Entry -- Tips
2. Data Entry -- Fill Blank Cells
3. Data Entry -- Convert Text to Numbers
4. Data Entry -- Increase Numbers With Paste Special

Excel Data Entry Videos
Number keypad
with Excel shortcuts:

Home Excel Tips Excel Files The Excel Store Blog Contact

Privacy Policy

Contextures Inc., Copyright ©2013
All rights reserved.

Debra Dalgleish

Last updated: September 19, 2012 12:20 AM