# Fill in the Empty Cells

Location:   http://www.mvps.org/dmcritchie/excel/fillempt.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
 A B C D E F 1 A1 B1 C1 D1 E1 F1 2 B2 C2 D2 E2 F2 3 C3 D3 E3 F3 4 F4 5 A5 B5 C5 D5 E5 F5 6 E6 F6 7 B7 C7 D7 E7 F7 8 D8 E8 F8

 A B C D E F 1 A1 B1 C1 D1 E1 F1 2 A1 B2 C2 D2 E2 F2 3 A1 B2 C3 D3 E3 F3 4 A1 B2 C3 D3 E3 F4 5 A5 B5 C5 D5 E5 F5 6 A5 B5 C5 D5 E6 F6 7 A5 B7 C7 D7 E7 F7 8 A5 B7 C7 D8 E8 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 1 Alex 2 3 4 Barb 5 6 Cleo 7 8

 A 1 Alex 2 Alex 3 Alex 4 Barb 5 Barb 6 Cleo 7 Cleo 8 Cleo

### 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
```

### 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
```

 A B C D E F 1 A1 B1 C1 D1 E1 F1 2 A1 B2 C2 D2 E2 F2 3 A1 B2 C3 D3 E3 F3 4 A1 B2 C3 D3 E3 F4 5 A5 B5 C5 D5 E5 F5 6 A5 B5 C5 D5 E6 F6 7 A5 B7 C7 D7 E7 F7 8 A5 B7 C7 D8 E8 F8

 A B C D E F 1 A1 B1 C1 D1 E1 F1 2 B2 C2 D2 E2 F2 3 C3 D3 E3 F3 4 F4 5 A5 B5 C5 D5 E5 F5 6 E6 F6 7 B7 C7 D7 E7 F7 8 D8 E8 F8
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.