How to Hide Blank Rows in Excel VBA (4 Useful Examples)

This article illustrates how to hide rows that are entirely blank or have blank cells in them using VBA code in Excel. Here we’ll use Excel’s built-in functions and properties to configure our code. Let’s dive into the examples to learn more.

How to Hide Blank Rows Using VBA in Excel: 4 Useful Examples

Let’s say we have a dataset of sale details of different products. It contains a couple of blank rows that we’re going to hide using simple VBA code.

In the following examples, we’ll use the Range.EntireRow property of VBA Excel to select an entire row that is entirely blank or has blank cells in it. Then we need to assign the Hidden property as True to hide the row.

Write Code in Visual Basic Editor

To hide blank rows, we need to open and write VBA code in the Visual Basic Editor. Follow the steps to open the Visual Basic Editor and write some code there.

• Go to the Developer tab from the Excel Ribbon.
• Click the Visual Basic option.

• In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.

Now put your code inside the visual code editor and press F5 to run it.

1. Hide Rows That Contain Blank Cells

1.1 Hide Rows Based on Predefined Source Range

Task: Hide rows that contain blank cells in the following dataset (B4:H13).

Problem Analysis: We need to run a loop through all the cells in the dataset (B4:H13) to find the blank cells (B8 and D10) first. Then we’ll select the entire rows (rows 8 and 10) corresponding to the blank cells and hide them.

Solution: To check each of the cells in the dataset, we’ll use the For Each…Next loop in the following code. And the If…Then…Else statement will check if the cell is blank or not. If the condition is true, it will apply VBA Range.EntireRow property to select the entire row to assign the Hidden property as true.

Code: Insert the following code in the Visual Basic Editor and press F5 to run it.

Sub HideBlankRows()
Dim val As Range
For Each val In Range("B4:H13").Cells
If val .Value = "" Then
    val .EntireRow.Hidden = True
End If
Next
End Sub

Output: We’ve successfully hidden rows 8 and 10 shown in the following dataset.

1.2 Hide Rows Based on Selection

Task: Hide rows that contain blank cells in the selected source range (B4:H9).

Problem Analysis: We need to run a loop through all the cells in the selected dataset (B4:H9) to find the blank cells (B8) first. Then we’ll select the entire row (row 8) corresponding to the blank cell and hide it.

Solution: In addition to the previous solution, we need to use the Application.Selection property to select our desired portion of the dataset.

Code: Insert the following code in the Visual Basic Editor and press F5 to run it.

Sub HideBlankRows()
Dim val As Range
For Each val In Selection
If val.Value = "" Then
     val.EntireRow.Hidden = True
End If
Next
End Sub

Output: We’ve successfully hidden row 8 shown in the following dataset. It didn’t hide row 10 as it was out of the selected range.

Read More: VBA to Hide Rows Based on Criteria in Excel

2. Select a Range and Then Hide Blank Rows

Task: Hide rows that are completely blank in the selected dataset. The code we’ll run should not hide a row that has one or more blank cells but is not completely blank.

Problem Analysis: We’ve selected the whole dataset and want to hide rows 10 and 12. These two rows are entirely blank. But we don’t want to hide row 8 which has a blank cell.

Solution: Use the COUNTA function as a worksheet function in VBA code to check each row whether is blank or not.

Code: Insert the following code in the visual basic editor and press F5 to run it.

Public Sub DeleteBlankRows()
    Dim cellRange As Range
    Dim selectRow As Range
    Set cellRange = Application.Selection
    If Not (cellRange Is Nothing) Then
        Application.ScreenUpdating = False
        For i = cellRange.Rows.Count To 1 Step -1
            Set selectRow = cellRange.Cells(i, 1).EntireRow
            If Application.WorksheetFunction.CountA(selectRow) = 0 Then
                selectRow.Hidden = True
            End If
        Next
        Application.ScreenUpdating = True
    End If
End Sub

Output: We’ve successfully hidden rows 10 and 12 shown in the following dataset.

3. Hide All Blank Rows in a Worksheet 

Task: Hide rows that are completely blank in the whole dataset. The code we’ll run should not hide a row that has one or more blank cells but is not completely blank.

Problem Analysis: We want to select the whole dataset with our VBA code automatically not manually as we did in the previous example.

Solution: We need to use the Worksheet.UsedRange property to select the whole dataset automatically.

Code: Insert the following code in the Visual Basic Editor and press F5 to run it.

Public Sub DeleteBlankRows()
    Dim cellRange As Range
    Dim selectRow As Range
    Set cellRange = ActiveSheet.UsedRange
    If Not (cellRange Is Nothing) Then
        Application.ScreenUpdating = False
        For i = cellRange.Rows.Count To 1 Step -1
            Set selectRow = cellRange.Cells(i, 1).EntireRow
            If Application.WorksheetFunction.CountA(selectRow) = 0 Then
                selectRow.Hidden = True
            End If
        Next
        Application.ScreenUpdating = True
    End If
End Sub

Output: We’ve successfully hidden rows 10 and 12 shown in the following dataset.

4. Auto Hide Rows Based on Blank Cells

Task: Hide the entire row whenever a blank cell is created in the dataset.

Code: Right-click the mouse on the sheet name and choose the View Code option.

Then put the following code inside the editor and save it.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim val As Range
    Application.ScreenUpdating = False
        For Each val In ActiveSheet.UsedRange
            If val.Value = "" Then
                val.EntireRow.Hidden = True
            End If
        Next val
    Application.ScreenUpdating = True
End Sub

Output: if we delete a cell data to make it blank, the entire row containing the blank cell will be hidden. For example, we deleted cell B6 data to make it blank. The VBA code got hidden row number 6 entirely.

Things to Remember

Once we run the VBA code, after completing the task, it loses the history. So, we cannot undo what we’ve done. That’s why we should also know how to unhide rows in Excel.

Download Practice Workbook

Conclusion

Now, we know how to hide blank rows using VBA in Excel with the help of suitable examples. Hopefully, it will help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• VBA to Hide Rows Based on Cell Value in Excel
• How to Hide Rows with Zero Values in Excel Using Macro
• Excel VBA: Unhide All Rows in Excel