Search Contextures Sites

Excel Data Entry -- Fill Blank Cells in Excel Column

Fill Blank Cells Manually
Fill Blank Cells Programmatically
   

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.

Watch the Fill Blank Cells Video

View the steps described below, in a short Fill Blank Cells video tutorial.

  


Start by selecting the empty cells:

  1. Select the cells in the column, starting in the row below the column heading.
  2. Choose Edit | Go To
  3. Click the Special button
  4. Select Blanks, click OK

 


Enter the formula to copy the value:

  1. Type an equal sign
  2. Press the up arrow on the keyboard -- this will enter a reference to the cell above -- cell A2 in this example
  3. 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.

  1. Select the entire column
  2. Choose Edit | Copy
  3. With the column still selected, choose Edit | Paste Special
  4. Select Values, click OK

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

Watch the Video

View the steps described above, in a short Fill Blank Cells video tutorial.

 

  

 

Fill Blank Cells Programmatically

If you frequently have to fill blank cells, you may prefer to use a macro. The following two 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
    
   
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 		Number keypad
with Excel shortcuts:
   

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright © 2009.
All rights reserved.

 

Last updated: February 6, 2010 11:33 PM