Excel -- Data Entry -- Fill Blank Cells
Fill Blank Cells
Fill Blank Cells Programmatically

Fill Blank Cells

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 Video

View the steps described below, in a short video clip.

  


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 video clip.

 

  

 

Fill Blank Cells Programmatically

If you frequently have to fill blank cells, you may prefer to use a macro. The following code will fill blank cells in the active column: 

Sub FillColBlanks()
'by Dave Peterson  2004-01-06
'fill blank cells in column with value above
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
   
   
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 		 

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 5, 2008 10:44 PM