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. Follow the instructions on this page to fill the blank cells, manually, or programmatically.
To fill the blank cells manually, you will select all the blanks, enter a simple formula in each cell, then convert the formulas to values. Follow the instructions below, or watch a video to see the steps.
NOTE: The "Fill Blank Cells" feature is available in my Contextures Excel Tools add-in
In this video, watch the steps to select and fill blank cells, with the value from the cell above. Then, use a mouse shortcut to change the formulas to values, so you can safely sort and filter the data.
To select the empty cells, use Excel's built in Go To Special feature:
The next step is to create a formula that will copy the value from the first heading above each blank cell.
Before you sort or filter the data, change the formulas to values. Otherwise, you'll end up with a mess.
Note: Do this carefully if other cells in the columns contain formulas. You don't want to accidentally change those formulas to values.
The blank cells are now filled in with values, and you can safely sort or filter the list.
If you frequently have to fill blank cells, you may prefer to use a macro. The following code examples fill blank cells in the active column. Each example uses a different method to find the last row, and to fill blank cells programmatically
For more information on finding the last row, see Ron de Bruin's page: Find last row, column or last cell. Ron explains the advantages and disadvantages of each method.
NOTE: The "Fill Blank Cells" feature is available in my Contextures Excel Tools add-in
The first example, from Dave Peterson, uses a formula to fill the cells, and pastes the results as values. The code uses the .SpecialCells(xlCellTypeLastCell) method to find the last row, and fills blanks from row 2 to the last row.
Sub FillColBlanks() 'by Dave Peterson 2004-01-06 'fill blank cells in column with value above 'https://www.contextures.com/xldataentry02.html 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
In the following code, Rick Rothstein uses the .Find method to calculate the last row. Instead of using a formula to fill from above, each cell gets its value from the cell above the first cell of the Area that it's in, using the Offset property.
Sub FillColBlanks_Offset() 'by Rick Rothstein 2009-10-24 'fill blank cells in column with value above 'https://www.contextures.com/xldataentry02.html Dim Area As Range, LastRow As Long On Error Resume Next LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, _ LookIn:=xlFormulas).Row For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _ SpecialCells(xlCellTypeBlanks).Areas Area.Value = Area(1).Offset(-1).Value Next End Sub
This example combines Dave Peterson's code (Example 1), with the special cells test from Ron de Bruin.
In Excel 2007, and earlier versions, there is a problem with special cells if there are more than 8192 different areas in the special cells range. This problem has been fixed in Excel 2010.
This code tries to count the areas, and if over the limit, it loops through the range in groups of 8000 rows.
Sub FillColBlanksSpecial() 'https://www.contextures.com/xldataentry02.html 'by Dave Peterson 2004-01-06 'fill blank cells in column with value above '2010-10-12 incorporated Ron de Bruin's test ' for special cells limit 'https://www.rondebruin.nl/win/s4/win003.htm Dim wks As Worksheet Dim rng As Range Dim rng2 As Range Dim LastRow As Long Dim col As Long Dim lRows As Long Dim lLimit As Long Dim lCount As Long On Error Resume Next lRows = 2 'starting row lLimit = 8000 Set wks = ActiveSheet With wks col = ActiveCell.Column Set rng = .UsedRange 'try to reset the lastcell LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set rng = Nothing lCount = .Columns(col).SpecialCells(xlCellTypeBlanks) _ .Areas(1).Cells.Count If lCount = 0 Then MsgBox "No blanks found in selected column" Exit Sub ElseIf lCount = .Columns(col).Cells.Count Then 'next line can be deleted MsgBox "Over the Special Cells Limit" Do While lRows < LastRow Set rng = .Range(.Cells(lRows, col), _ .Cells(lRows + lLimit, col)) _ .Cells.SpecialCells(xlCellTypeBlanks) rng.FormulaR1C1 = "=R[-1]C" lRows = lRows + lLimit Loop Else Set rng = .Range(.Cells(2, col), _ .Cells(LastRow, col)) _ .Cells.SpecialCells(xlCellTypeBlanks) rng.FormulaR1C1 = "=R[-1]C" End If 'replace formulas with values With .Cells(1, col).EntireColumn .Value = .Value End With End With End Sub
This macro example is from Alex Blakenburg, who had numbers entered as text in his data. He noticed that the other macros converted those to real numbers, with the .Value = .Value code.
Alex's data doesn't always start in row 1, so the macro starts from the active cell, and stops at the last row in the active column, after resetting the last cell.
Sub FillSelBlanks_Num() 'from Alex Blakenburg 'use FillDown to prevent text numbers ' from converting to real numbers 'starts from active cell Dim RowEnd As Long Dim RowStart As Long Dim ColStart As Long Dim RowNo As Long Dim wks As Worksheet Set wks = ActiveSheet ' Reset end of sheet and ' capture end row and column nos Call ResetLastCell With wks.UsedRange RowEnd = .Rows(.Rows.Count).Row End With ' Start from selected cell ' check next row is blank, fill down RowStart = ActiveCell.Row ColStart = ActiveCell.Column RowNo = RowStart ' to limit errors due to invoking ' accidentally, check that next row ' is blank. If not, exit routine With wks If Not IsEmpty(.Cells(RowStart + 1, _ ColStart)) _ And .Cells(RowStart + 1, _ ColStart).Value <> "" Then MsgBox "Expect next row to be blank" _ & " - exiting routine" Exit Sub End If ' Cycle through rows, ' copy group value down Do Until RowNo >= RowEnd RowNo = RowNo + 1 If IsEmpty(.Cells(RowNo, _ ColStart).Value) _ Or .Cells(RowNo, _ ColStart).Value = "" Then ' Use previous row to try to transfer ' both the text attribute and value ' for Numeric text. Using the variable ' converted numeric text to a number .Cells(RowNo, ColStart).FillDown End If Loop End With End Sub '============================= Sub ResetLastCell() Dim lLastRow As Long Dim lLastColumn As Long Dim lRealLastRow As Long Dim lRealLastColumn As Long Dim rng As Range Dim wks As Worksheet Set wks = ActiveSheet ' Find last row,column ' special cells method With wks.Range("A1") _ .SpecialCells(xlCellTypeLastCell) lLastRow = .Row lLastColumn = .Column End With ' Find backwards from A1 ' to last non-blank row With wks.Cells lRealLastRow = .Find("*", Range("A1"), _ xlFormulas, , xlByRows, xlPrevious).Row ' Find backwards from A1 ' to last non-blank column lRealLastColumn = .Find("*", Range("A1"), _ xlFormulas, , xlByColumns, _ xlPrevious).Column End With With wks 'Delete from row after real last row 'to last row, per special cells method If lRealLastRow < lLastRow Then .Range(.Cells(lRealLastRow + 1, 1), _ .Cells(lLastRow, 1)).EntireRow.Delete End If 'Delete from column after real last column 'to last column per special cells method If lRealLastColumn < lLastColumn Then .Range(.Cells(1, lRealLastColumn + 1), _ .Cells(1, lLastColumn)) _ .EntireColumn.Delete End If Set rng = .UsedRange 'Resets last cell End With End Sub
Here is a longer version of the Fill Blank Cells video, and the transcript for this video is below.
Sometimes in Excel, you'll end up with data like this, possibly exported from another system where you've got headings, but blank cells below those headings.
Here we can see region and it's only listed once, and then blank to the end of the region, and the employees in each region. You might have one or multiple employees, but again, blank below those employee names.
This is fine for reading the list, but if you want to work with the data, perhaps filter it or sort things, then you need to fill in these blank cells. Here's a quick way to do that.
First, we'll select columns A and B where there are blanks.
All the blank cells are selected now and we're going to put in a very simple formula that just says get the value from the cell above.
That puts that same formula into all the selected cells.
Now we want to change these formulas to values so that we can move things around without having the values change.
Now everything in here is a value instead of a formula, and you can sort or filter without any problems.
To see the report with blank cells, and test the Fill Blanks macros, you can download the sample file. The file is zipped, and is in xlsm format, and contains macros.
Increase Numbers With Paste Special
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: March 3, 2021 4:02 PM