Fill in the Empty Cells

This macro will fill in empty cells with the content of the cell above it, providing the cell above is also within the selection range.

FillEmpty -- Requires XL95 and up

Provides a solution for range across multiple columns.

	A	B	C	D	E
1	Jan		C4 has 1-space		ffff
2		aa
3
4	Feb			ddd
5		bb
6			C6	eee
7	Mar
8

 

	A	B	C	D	E
1	Jan		C4 has 1-space		ffff
2	Jan	aa	C4 has 1-space		ffff
3	Jan	aa	C4 has 1-space		ffff
4	Feb	aa		ddd	ffff
5	Feb	bb		ddd	ffff
6	Feb	bb	C6	eee	ffff
7	Mar	bb	C6	eee	ffff
8	Mar	bb	C6	eee	ffff

A couple of Excel Shortcuts

• Copy a formula from the cell above the active cell into the cell or the formula bar.  Applies only to a single cell.    Ctrl + '  (apostrophe)
• Fill Down:  Ctrl+D 
  Like the fill-handle, Ctrl + D,  indiscriminently copies data from cells within the first row of the selected area downward like the fill-handle.

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 impreoves 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 30/06/98 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

Related Items

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 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 Deleting Blank cells at Chip Pearson's site http://www.CPearson.com/excel.htm 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.

---

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 mailto:DMcRitchie@msn.com.