Filling in empty cells with the content of the cell above each from within the selection range.
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.
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
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.
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 SubA 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
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 SubI 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
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
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.This is actually the opposite of the first example on this page.
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 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
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