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

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.

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.

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

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

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

Similar Readings

• How to Return Value if Cell is Blank (12 Ways)
• Find, Count and Apply Formula If a Cell is Not Blank (With Examples)
• How to Highlight Blank Cells in Excel (4 Fruitful Ways)
• Null vs Blank in Excel
• How to Delete Blank Cells in Excel and Shift Data Up

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:

[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

• How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas
• Find If Cell is Blank in Excel (7 Methods)
• If Cell is Blank Then Show 0 in Excel (4 Ways)
• How to Apply Conditional Formatting in Excel If Another Cell Is Blank
• Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)
• If a Cell Is Blank then Copy Another Cell in Excel (3 Methods)