Fill in the Empty Cells

Location:   http://www.mvps.org/dmcritchie/excel/fillempt.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]
Filling in empty cells with the content of the cell above each from within the selection range.

FillEmpty -- Non Macro Solution

A non macro solution posting by Eddie Griffiths for filling in empty cells with the content of the cell above each from within the selection range.
You don't need a macro.
Make a selection (Multiple columns, for instance)
F5 Special, goto blanks
in the formala bar add =A1
then press Ctrl + Enter     (Ctrl + D, fill down would also work)
reselect the original range in order to convert formulas
Copy with Ctrl+C
Edit, Paste Special, Values
 ABCDEF
1A1B1C1D1 E1F1
2 B2 C2D2E2F2
3   C3D3E3F3
4       F4
5A5B5C5D5 E5F5
6      E6F6
7 B7 C7D7E7F7
8    D8 E8F8
   
 ABCDEF
1A1B1C1D1 E1F1
2 A1B2C2 D2E2F2
3 A1 B2C3D3 E3F3
4 A1 B2 C3 D3 E3F4
5A5B5C5D5 E5F5
6 A5 B5 C5 D5E6F6
7 A5B7C7 D7E7F7
8 A5 B7 C7D8E8 F8
Explanation -- if one is needed: You can choose any selection area including multiple columns. When you pick out the blanks with Special cells the active cell will be the first blank cell and you use the cell above it in the formula.
 
Special Cells restricts itself to the Used Area so you can select multiple columns if you like and not be concerned that the entire column is mostly unused.

Fill_Empty, Macro solution (#fill_empty)

Some of this material is also found with more dscriptive material on my Fill Handle page.

Programming solution: Equivalent to the previous topic, but bolding the original text descriptions.  The following macro has no loops so should run like greased lightening.  The Fill_Empty macro below fills in truly empty cells.  The FillEmpty in the next topic fills in cells that "look" empty (which may contain spaces).  The following macro solution has no loops.

Sub Fill_Empty()
 '--David McRitchie,  2003-07-24,  fillempt.htm 
 '--Macro version of -- Excel -- Data Entry -- Fill Blank Cells
 'http://www.contextures.com/xlDataEntry02.html
 'http://www.pcworld.com/shared/printable_articles/0,1440,9346,00.html
    Dim oRng As Range
    Set oRng = Selection
    Selection.Font.Bold = True
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Font.Bold = False
    Selection.FormulaR1C1 = "=R[-1]C"
    oRng.Copy
    oRng.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
End Sub 
A request to the opposite of the before and after, so that cells that duplicate the cell immediately above are cleared can be found in topic later.
   
 A
1Alex
2 
3 
4Barb
5 
6Cleo
7 
8 
   
 A
1Alex
2Alex
3Alex
4Barb
5Barb
6Cleo
7Cleo
8Cleo

Fill Empty -- Previous Macro Solutions (#previous)

A macro may be more useful, this will replace empty cells and cells that look empty by the cell above thoroughout the selected area.  This has been written to work with values not formulas.

Here is a Deja posting with some better answers using copy/paste than the solution I provide below. 

Sub FillEmpty()
  'Tom Ogilvy, 1999/12/14 programming
  ' Revised David McRitchie, 2000-11-25 programming
  Application.ScreenUpdating = False
  Application.Calculation = xlManual
  Dim cell As Range
  For Each cell In Intersect(Selection, _
            ActiveSheet.UsedRange)
    If Trim(cell) = "" And cell.Row > 1 Then
      cell.NumberFormat = cell.Offset(-1, 0).NumberFormat
      cell.Value = cell.Offset(-1, 0).Value
    End If
  Next cell
  Application.Calculation = xlAutomatic  'xlCalculationAutomatic
  Application.ScreenUpdating = False
  End Sub
I added TRIM, numberformat, and turning off/on screen updating and calculation which improves speed about 10 fold.  Also because there is nothing above row 1, a check for row 1.  Finally to make it even more generic, I restricted the selection scope to the used range.

My earlier attempt at this.

Sub FillEmpty()
' Fill in empty cells with value of cell above if also in range. 06/30/1998
' David McRitchie  http://www.mvps.org/dmcritchie/excel/fillempt.htm
    Dim WithWhat As Variant
    iRows = Selection.Rows.Count
    iColumns = Selection.Columns.Count
    For iC = 1 To iColumns
       WithWhat = Selection.Item(1, iC).Value
       For iR = 1 To iRows
          If Selection.Item(iR, iC).Value = "" Then
             Selection.Item(iR, iC).Value = WithWhat
          Else
             WithWhat = Selection.Item(iR, iC).Value
          End If
       Next iR
    Next iC
End Sub

FillBlanks -- works in Excel 5 version and up

Provides a solution for Excel 5, range in single column only.
Sub FillBlanks()
    'FillBlanks Macro written 1998-06-30 by Gary Barlow
    Set topcell = Cells(1, ActiveCell.Column)
    Set bottomcell = Cells(16384, ActiveCell.Column)
    If IsEmpty(topcell) Then Set topcell = topcell.End(xlDown)
    If IsEmpty(bottomcell) Then Set bottomcell = bottomcell.End(xlUp)
    Range(topcell, bottomcell).Select
    Selection.SpecialCells(xlBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
End Sub

Clearing Cells

Clear Duplicate Cells Immediately Below   (#clear_dupcells_below)

While this is the opposite of Fill_Empty (the first example), we must use a loop for this.  The clearing is based on value not on formulas or trimmed comparisons.
Sub clear_dupcells_below()
  'D.McRitchie,  2006-02-01 www.mvps.org/dmcritchie/excel/fillempt.htm
  Dim rng As Range, iRows As Long, iColumns As Long
  Dim ic As Long, ir As Long
  Set rng = Intersect(Selection, ActiveSheet.UsedRange)
  Dim WithWhat As Variant
  iRows = rng.Rows.Count
  iColumns = rng.Columns.Count
  If iRows < 2 Then Exit Sub
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  For ic = iColumns To 1 Step -1
     For ir = iRows To 2 Step -1
      If rng.Item(ir, ic).Value = _
         rng.Item(ir - 1, ic).Value Then
         rng.Item(ir, ic).Formula = ""
      End If
     Next ir
  Next ic
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = False
End Sub
   
 ABCDEF
1A1B1C1D1 E1F1
2 A1B2C2 D2E2F2
3 A1 B2C3D3 E3F3
4 A1 B2 C3 D3 E3F4
5A5B5C5D5 E5F5
6 A5 B5 C5 D5E6F6
7 A5B7C7 D7E7F7
8 A5 B7 C7D8E8 F8
   
 ABCDEF
1A1B1C1D1 E1F1
2 B2 C2D2E2F2
3   C3D3E3F3
4       F4
5A5B5C5D5 E5F5
6      E6F6
7 B7 C7D7E7F7
8    D8 E8F8
This is actually the opposite of the first example on this page.
This page contains some VBA macros.  If you need assistance to install or to use a macro please refer to Getting Started with Macros.  For more depth see Install a Macro or User Defined Function  on my Formula page.

Speed and efficiency considerations can be seen in Proper, and other Text changes and in Slow Response.

John Walkenbach in his March 1999 spreadsheets column in PC World describes How to Duplicate Values in an Excel List without using a macro.  I looked at it but did not realize that you could also select multiple columns as in my macro, until having read a newsgroup posting by Eddie Griffiths in a later thread, when I looked at both approaches once more.  Don't forget to reselect the original range copy, paste special, to convert the formulas to values.  Use John Walkenbach's PC World Column index, the PC World site is too slow for any practical use.  This will at least get to the correct article, though you still have to endure frames.

Werner Janz fill_empties [alt] is similar to my fillempty, but uses items (less coding) rather than columns and rows in processing the range.  Will pick up out of values above to fill in empty cells which may be an advantage or disadvantage.  Chip Pearson in the same thread uses On Error Resume Next instead of testing that row is beyond row 1 of spreadsheet.

The antithesis of FillEmpty (for a single column) can be found in topic Delete Cells/Rows in Range, based on empty cells, or cells with specific values;  Deleting Blank cells at Chip Pearson's site Deleting Duplicate Rows, and duplicate cells; also an article by John Walkenbach Delete All Empty Rows in a Flash (Oct 1997 PC World).  If you delete rows at end of spreadsheet and you might also review my article Reset Last Cell Used, particularly if your Excel is XL95.

Excel Keyboard Shortcuts


since June 30, 1998. 

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2007,  F. David McRitchie,  All Rights Reserved