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

Get FREE Advanced Excel Exercises with Solutions!

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

VBA Code to Fill Blank Cells with Value Above in Excel VBA


Download Practice Workbook

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


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

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.

Data Set to Fill Blank Cells with Value Above in Excel VBA

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

VBA Code to Fill Blank Cells with Value Above in Excel VBA

⧭ 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.

Output to Fill Blank Cells with Value Above in Excel VBA

Read More: Fill Blank Cells with Value Above in Excel (4 Methods)


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

VBA Code to Fill Blank Cells with Value Above in Excel VBA

⧭ 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 range F4:G13 of the worksheet.

Output to Fill Blank Cells with Value Above in Excel VBA

Read More: How to Fill Blank Cells with Formula in Excel (2 Easy Methods)


Similar Readings


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

VBA Code to Fill Blank Cells with Value Above in Excel VBA

⧭ 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.

Read More: How to Autofill Blank Cells in Excel with Value Above (5 Easy Ways)


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.


Conclusion

So, these are the methods to fill the blank cells within a data set with values from the above cells. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo