How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)

In this article, I’ll show you how you can fill the blank cells within a range in Excel with the value available immediately above using VBA.

Fill Blank Cells with Value Above in Excel VBA (Quick View)

Sub Fill_Blank_Cells_with_Value_Above()

Set Worksheet = Worksheets("Sheet1")
Set Dataset = Worksheet.Range("B4:D13")

For i = 1 To Dataset.Columns.Count
    For j = 1 To Dataset.Rows.Count
        If Dataset.Cells(j, i) = "" Then
            Dataset.Cells(j, i) = Dataset.Cells(j - 1, i)
        End If
    Next j
Next i

End Sub

How to Fill Blank Cells with Value Above in Excel VBA: 3 Methods

Here we’ve got a data set with some dates, along with some product names that were supposed to be given delivery on that date, and their delivery quantities.

Look carefully and you’ll find that some cells within the range are blank. Our objective today is to fill up the blank cells with values available immediately above. We’ll develop 3 methods to fulfill this objective successfully using VBA.

Method 1: Developing a Macro to Fill Blank Cells within the Original Data Set with Value Above

First of all, we’ll develop a Macro to fill the blank cells of the range within the original data set with the value available above.

Use the following VBA code for this purpose:

⧭ VBA Code:

Sub Fill_Blank_Cells_with_Value_Above()

Set Worksheet = Worksheets("Sheet1")
Set Dataset = Worksheet.Range("B4:D13")

For i = 1 To Dataset.Columns.Count
    For j = 1 To Dataset.Rows.Count
        If Dataset.Cells(j, i) = "" Then
            Dataset.Cells(j, i) = Dataset.Cells(j - 1, i)
        End If
    Next j
Next i

End Sub

⧭ Note:

The first 2 lines of the code declare the inputs to the code (Worksheet and Dataset). Don’t forget to change these according to your needs.

⧭ Output:

Run the code. It’ll fill all the blank cells of the data set with the above values.

Method 2: Developing a Macro to Fill Blank Cells with Value Above in a Different Location

In the earlier method, we developed a Macro to fill the blank cells within a data set with values above within the original data set. Now we’ll develop a Macro to fill the blank cells in a different location (F4:H13 here)

The VBA code will be:

⧭ VBA Code:

Sub Fill_Blank_Cells_in_a_Different_Location()

Set Worksheet = Worksheets("Sheet1")
Set Dataset = Worksheet.Range("B4:D13")
Set Output = Worksheet.Range("F4:H13")

For i = 1 To Dataset.Columns.Count
    For j = 1 To Dataset.Rows.Count
        If Dataset.Cells(j, i) <> "" Then
            Output.Cells(j, i) = Dataset.Cells(j, i)
        Else
            Output.Cells(j, i) = Output.Cells(j - 1, i)
        End If
    Next j
Next i

End Sub

⧭ Note:

Here the first 3 lines of the code declare the inputs to the code (Worksheet, Dataset, and Output Range). Don’t forget to change these according to your needs.

⧭ Output:

Run the code. It’ll fill all the blank cells of the data set with values above in the range F4:G13 of the worksheet.

Method 3: Developing a User-Defined Function to Fill Blank Cells with Value Above (in a Different Location)

Finally, we’ll create a user-defined function that’ll fill the blank cells of a data set with the values above.

We’ll use the following VBA code for this purpose:

⧭ VBA Code:

Function FillBlankCells(Dataset As Range)

Dim Output() As Variant
ReDim Output(Dataset.Rows.Count - 1, Dataset.Columns.Count - 1)

For i = 1 To Dataset.Columns.Count
    For j = 1 To Dataset.Rows.Count
        If Dataset.Cells(j, i) <> "" Then
            Output(j - 1, i - 1) = Dataset.Cells(j, i)
        Else
            Output(j - 1, i - 1) = Output(j - 2, i - 1)
        End If
    Next j
Next i

FillBlankCells = Output

End Function

⧭ Output:

Select a range equal to the original data set in the worksheet and enter the formula:

=FillBlankCells(B4:D13)

[Here B4:D13 is the range of my data set. You use your one.]

Then press CTRL + SHIFT + ENTER (Not necessary in Office 365). It’ll return the data set with the blank cells filled with values from the above cells.

Things to Remember

The formula used in method 3 is an Array Formula. So, you have to use CTRL + SHIFT+ ENTER to use the formula unless you are in Office 365.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Fill Blank Cells.xlsm

Conclusion

So, these are the methods to fill the blank cells within a data set with values from the above cells. If you have any further questions related to the topic, feel free to leave a comment in the comment section