# Fill Blank Cells in Excel Column

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.

NOTE: The "Fill Blank Cells" feature is available in my Contextures Excel Tools add-in

## Fill Blank Cells Manually

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.

## Video: Fill Blank Cells Manually

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.

## Fill Blank Cells Manually in Excel 2010 / 2007

### Select the Empty Cells

To select the empty cells, use Excel's built in Go To Special feature:

1. Select columns A and B, that contain the blank cells.
2. On the Ribbon's Home tab, click Find & Select
3. Click Go To Special

4. In the Go To Special dialog box, click Blanks, then click OK

### Create a Formula

The next step is to create a formula that will copy the value from the first heading above each blank cell.

1. With the blank cells selected, type an equal sign, to start the formula.
2. On the keyboard, press the up arrow. This enters a reference to the cell above - cell A2 in this example.

3. Press the Ctrl key and tap the Enter key, to enter the formula in all the selected cells.

### Change Formulas to Values

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.

1. Select the entire columns where you filled in the blanks with formulas -- columns A:B in this example
2. On the Ribbon's Home tab, click Copy.
3. With the columns still selected, click the drop down arrow on the Paste command
4. Click Paste Values

The blank cells are now filled in with values, and you can safely sort or filter the list.

## Video: Fill Blank Cells Manually in Excel 2003

View the steps for filling in blank cells in Excel 2003, in this short video tutorial. Written instructions are below the video.

## Fill Blank Cells Manually in Excel 2003

Follow these steps to fill blank cells in an Excel 2003 worksheet.

### Start by selecting the empty cells:

1. Select the cells in the column, starting in the row below the column heading.
2. Choose Edit | Go To
3. Click the Special button
4. Select Blanks, click OK

### Enter the formula to copy the value:

1. Type an equal sign
2. Press the up arrow on the keyboard -- this will enter a reference to the cell above -- cell A2 in this example
3. Hold the Ctrl key and press Enter -- this enters the formula in all selected cells

### Change the formulas to values:

In order to sort or filter the data, the formulas must be changed to values.

1. Select the entire column
2. Choose Edit | Copy
3. With the column still selected, choose Edit | Paste Special
4. Select Values, click OK

Note: Do this carefully if there are other cells in the range which contain formulas.

## Fill Blank Cells - Macros

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

### Fill Blank Cells Macro - Example 1

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

### Fill Blank Cells Macro - Example 2

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

### Fill Blank Cells Macro - Example 3

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

### Fill Blank Cells Macro - Example 4

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

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.

Data Entry Tips

## Don't Miss Our Excel Tips

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.

## Search Contextures

Search Contextures Sites

Search Contextures Sites

#### More Tutorials

Data Entry Tips

.

Last updated: October 15, 2018 12:54 PM