How to Delete Hidden Rows in Excel VBA (Step-by-Step Analysis)

In this article, I’ll show you how you can delete hidden rows from a worksheet with VBA in Excel. You’ll learn to delete hidden rows from both a selected range of a worksheet and the whole worksheet with examples involving Macro and UserForm.

How to Delete Hidden Rows in Excel VBA (Quick View)

Sub Delete_Hidden_Rows_from_Whole_Worksheet()
For i = Rows.Count To 1 Step -1
    If Rows(i).EntireRow.Hidden = True Then
        Rows(i).EntireRow.Delete
    End If
Next i
End Sub

How to Delete Hidden Rows in Excel VBA: Step-by-Step Analysis

Here we’ve got a worksheet called Sheet1 in Excel where the rows from 3 to 5 and 8 to 11 are hidden.

Our objective today is to learn how we can delete the hidden rows from this data set, both from a selected range and the whole worksheet.

Method 1: Delete Hidden Rows from a Specific Range of Cells by Running a Forward Iteration in Excel VBA

First of all, let’s learn to delete hidden rows from a specific range of cells with Excel VBA. We’ll use two forward iterations for this purpose. Let’s learn the procedure through a step-by-step analysis.

We’ll delete the hidden rows from the range A1:A7 in this method.

⧪ Step 1: Declaring the Variable to Count the Number of Hidden Rows

First of all, we’ll have to count the number of hidden rows within the selected range. To accomplish this, we need to declare a variable. We have named it Number_of_Hidden_Rows.

Dim Number_of_Hidden_Rows As Integer
Number_of_Hidden_Rows = 0

⧪ Step 2: Counting the Number of Hidden Rows

Next, we’ll iterate through a for-loop to count the number of hidden rows.

For i = 1 To Range("A1:A7").Rows.Count
    If Range("A1:A7").Cells(i, 1).EntireRow.Hidden = True Then
        Number_of_Hidden_Rows = Number_of_Hidden_Rows + 1
    End If
Next i

⧪ Step 3: Deleting the Hidden Rows

Finally, we’ll iterate through another for-loop to delete the hidden rows.

For i = 1 To Range("A1:A7").Rows.Count - Number_of_Hidden_Rows
    If Range("A1:A7").Cells(i, 1).EntireRow.Hidden = True Then
        Range("A1:A7").Cells(i, 1).EntireRow.Delete
        i = i - 1
    End If
Next i

So, the complete VBA code will be:

⧭ VBA Code:

Sub Delete_Hidden_Rows_from_a_Range()

Dim Number_of_Hidden_Rows As Integer
Number_of_Hidden_Rows = 0

For i = 1 To Range("A1:A7").Rows.Count
    If Range("A1:A7").Cells(i, 1).EntireRow.Hidden = True Then
        Number_of_Hidden_Rows = Number_of_Hidden_Rows + 1
    End If
Next i

For i = 1 To Range("A1:A7").Rows.Count - Number_of_Hidden_Rows
    If Range("A1:A7").Cells(i, 1).EntireRow.Hidden = True Then
        Range("A1:A7").Cells(i, 1).EntireRow.Delete
        i = i - 1
    End If
Next i

End Sub

⧭ Output:

Run the code, and it’ll delete the hidden rows (row 3, 4, and 5)  from the range A1:A7.

Read More: How to Use Macro to Delete Rows Based on Criteria in Excel

Method 2: Delete Hidden Rows from a Specific Range of Cells by Running a Backward Iteration in Excel VBA

Now we’ll learn to delete the hidden rows from a specific range of cells by running a backward iteration.

Again, we’ll delete the hidden rows from the range A1:A7.

To delete the hidden rows through backward iteration, you needn’t count the total number of hidden rows (Step 1 and 2).

Running only a backward iteration through all the rows of the range is enough.

So, the complete VBA code to delete the hidden rows from the range A1:A7 will be:

⧭ VBA Code:

Sub Delete_Hidden_Rows_from_a_Range_2()

For i = Range("A1:A7").Rows.Count To 1 Step -1
    If Range("A1:A7").Cells(i, 1).EntireRow.Hidden = True Then
        Range("A1:A7").Cells(i, 1).EntireRow.Delete
    End If
Next i

End Sub

⧭ Output:

Run the code, and it’ll again delete the hidden rows from the range A1:A7.

Method 3: Delete Hidden Rows from the Whole Worksheet by Running a Backward Iteration in Excel VBA

Finally, we’ll learn to delete hidden rows from the whole worksheet. We’ll again use a backward iteration here.

We have to iterate through all the rows of the active worksheet.

The VBA code will be:

⧭ VBA Code:

Sub Delete_Hidden_Rows_from_Whole_Worksheet()

For i = Rows.Count To 1 Step -1
    If Rows(i).EntireRow.Hidden = True Then
        Rows(i).EntireRow.Delete
    End If
Next i

End Sub

⧭ Output:

Run the code. It’ll delete the hidden rows from the whole worksheet.

⧭ Note:

As this code runs through all the rows of the worksheet, it may take a bit of time. So have patience and wait till the process ends.

Read More: How to Use VBA to Delete Empty Rows in Excel

Examples of Deleting Hidden Rows in Excel VBA

We’ve learned to delete the hidden rows from a worksheet with Excel VBA. Now we’ll explore a few examples of this.

Example 1: Developing a Macro to Delete Hidden Rows in Excel VBA

First of all, we’ll develop a Macro to delete hidden rows from a worksheet. It’ll delete hidden rows from both a selected range of cells of a worksheet and the whole worksheet, according to the user’s choice.

The VBA code will be:

⧭ VBA Code:

Sub Delete_Hidden_Rows()

Argument1 = "Enter 1 to Delete Hidden Rows from a Specific Range: "
Argument2 = "Enter 2 to Delete Hidden Rows from the Whole Worksheet:"

Input1 = InputBox(Argument1 + vbNewLine + vbNewLine + Argument2)

If Input1 = 1 Then
    Input2 = InputBox("Enter the Specific Range:")
    For i = Range(Input2).Rows.Count To 1 Step -1
        If Range(Input2).Cells(i, 1).EntireRow.Hidden = True Then
            Range(Input2).Cells(i, 1).EntireRow.Delete
        End If
    Next i

ElseIf Input1 = 2 Then
    For i = Rows.Count To 1 Step -1
        If Rows(i).EntireRow.Hidden = True Then
            Rows(i).EntireRow.Delete
        End If
    Next i

Else
    MsgBox "Enter Either 1 or 2.", vbExclamation

End If

End Sub

⧭ Output:

Run the code. An input box will ask you to enter 1 to delete hidden rows from a specific range, or 2 to delete hidden rows from the whole worksheet.

Here I’ve inserted 1 to delete hidden rows from a specific range.

Then click OK. As I’ve selected a specific range, another input box will ask me to enter the specific range. Here I’ve put A1:A7.

Click OK. The hidden rows will be deleted from the range A1:A7.

Read More: How to Apply VBA to Delete Rows with Specific Data in Excel

Example 2: Developing a UserForm to Delete Hidden Rows in Excel VBA

Finally, we’ll develop a UserForm to delete hidden rows in Excel VBA. Let’s learn it step-by-step.

⧪ Step 1: Opening a New UserForm

Go to the Insert > UserForm button in the Visual Basic Editor to enter a new UserForm.

⧪ Step 2: Dragging Tools to the UserForm

A new UserForm called UserForm1 will open. Drag 2 Labels, 2 ListBoxes, 1 TextBox, and 1 CommandButton to the UserForm as shown in the figure below.

Change the displays of the Labels to Delete Hidden Rows from: and Range: respectively.

Also, change the display of the CommandButton to OK.

⧪ Step 3: Writing Code for ListBox1

Double-click on ListBox1. A private subprocedure called ListBox1_Click will open. Insert the following code there.

Private Sub ListBox1_Click()

On Error GoTo LB1

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
        Worksheets(UserForm1.ListBox1.List(i)).Activate
        Exit For
    End If
Next i

If UserForm1.TextBox1.Text <> "" Then
    ActiveSheet.Range(UserForm1.TextBox1.Text).Select
End If

Exit Sub

LB1:
    x = 21

End Sub

⧪ Step 4: Writing Code for ListBox2

Next, double-click on ListBox2. Another private subprocedure called ListBox2_Click will open. Insert the following code there.

Private Sub ListBox2_Click()

If UserForm1.ListBox2.Selected(0) = True Then
    UserForm1.Label2.Visible = True
    UserForm1.TextBox1.Visible = True
End If

If UserForm1.ListBox2.Selected(1) = True Then
    UserForm1.Label2.Visible = False
    UserForm1.TextBox1.Visible = False
End If

End Sub

⧪ Step 5: Writing Code for TextBox1

Then double-click on TextBox1. A private subprocedure called TextBox1_Change will open. Insert the following code there.

Private Sub TextBox1_Change()

On Error GoTo TB1

ActiveSheet.Range(UserForm1.TextBox1.Text).Select

Exit Sub

TB1:
    x = 21

End Sub

⧪ Step 6: Writing Code for CommanButton1

Finally, double-click on CommandButton1 and insert this code for the private subprocedure called CommandButton1_Click.

Private Sub CommandButton1_Click()

On Error GoTo CB1

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
        Active_Sheet = UserForm1.ListBox1.List(i)
        Exit For
    End If
Next i

If UserForm1.ListBox2.Selected(0) = True Then
    For i = Range(UserForm1.TextBox1.Text).Rows.Count To 1 Step -1
        If Range(UserForm1.TextBox1.Text).Cells(i, 1).EntireRow.Hidden = True Then
            Range(UserForm1.TextBox1.Text).Cells(i, 1).EntireRow.Delete
        End If
    Next i

ElseIf UserForm1.ListBox2.Selected(1) = True Then
    For i = Rows.Count To 1 Step -1
        If Rows(i).EntireRow.Hidden = True Then
        Rows(i).EntireRow.Delete
    End If
Next i

Else
    MsgBox "Choose One between Specific Range or Whole Worksheet.", vbExclamation

End If

CB1:
    x = 21

Unload UserForm1

End Sub

⧪ Step 6: Writing Code for Running the UserForm

In the end, insert a new Module and write this code for running the UserForm.

Sub Run_UserForm()

UserForm1.Caption = "Delete Hidden Rows"

UserForm1.ListBox1.ListStyle = fmListStyleOption
UserForm1.ListBox2.ListStyle = fmListStyleOption

For i = 1 To Sheets.Count
    UserForm1.ListBox1.AddItem Sheets(i).Name
Next i

For i = 0 To UserForm1.ListBox1.ListCount - 1
   If UserForm1.ListBox1.List(i) = ActiveSheet.Name Then
        UserForm1.ListBox1.Selected(i) = True
        Exit For
    End If
Next i

UserForm1.ListBox2.AddItem "Specific Range"
UserForm1.ListBox2.AddItem "Whole Worksheet"

UserForm1.Label2.Visible = False
UserForm1.TextBox1.Visible = False

Load UserForm1
UserForm1.Show

End Sub

⧪ Step 7: Running the UserForm

Your UserForm is now ready to use. Run the Macro called Run_UserForm. A UserForm with the caption Delete Hidden Rows will load.

Select the worksheet name from the list of worksheets from which you want to delete the hidden rows. Then choose either Specific Range or Whole Worksheet.

If you choose Specific Range, a TextBox will open asking you to enter the specific range.

Enter the specific range there. I’ve entered A1:A7.

Then click OK. You’ll find all the hidden rows deleted from the range A1:A7 of Sheet1.

Also, if you had selected Whole Worksheet and then clicked OK, all the hidden rows from Sheet1 would have been deleted.

Things to Remember

In this article, I mainly focused on how to delete hidden rows from a worksheet. To learn how to add hidden rows, visit this link.

Download Practice Workbook

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

Conclusion

So these are the ways to delete hidden rows from a specific range of a worksheet or the whole worksheet with VBA in Excel. Hope the step-by-step analysis of the methods along with the examples made everything pretty clear for you. Do you have any questions? Feel free to ask us.

Related Articles

• How to Filter and Delete Rows with VBA in Excel
  
• How to Apply VBA Code to Delete Rows Based on Multiple Cell Value
• VBA to Delete Every Other Row in Excel
• Excel VBA: Delete Row If Cell Is Blank
• How to Delete Row Using Macro If Cell Contains 0 in Excel
• Excel VBA to Delete Row If Cell Contains Partial Text