Al Arafat Siddique
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Useful Examples to Hide Blank Rows Using VBA in Excel
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 SubOutput: 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: Excel Macro: Hide Rows Based on Cell Text in Excel (3 Simple Methods)
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 SubOutput: We’ve successfully hidden rows 10 and 12 shown in the following dataset.
Read More: How to Hide Rows Based on Cell Value in Excel (5 Methods)
Similar Readings
- VBA to Hide Rows Based on Cell Value in Excel (14 Examples)
- VBA to Hide Rows Based on Criteria in Excel (15 Useful Examples)
- Hidden Rows in Excel: How to Unhide or Delete Them?
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 SubOutput: We’ve successfully hidden rows 10 and 12 shown in the following dataset.
Read More: Formula to Hide Rows in Excel (7 Methods)
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.
Read More: Hide Rows Based on Cell Value with Conditional Formatting in Excel
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.
Conclusion
Now, we know how to hide blank rows using VBA in Excel with the help of suitable examples. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
