How to Delete Hidden Rows in Excel VBA (A Detailed 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

VBA Code to Delete Hidden Rows in Excel VBA


Download Practice Workbook

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


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.

Worksheet to Delete Hidden Rows in Excel VBA

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

VBA Code to Delete Hidden Rows in Excel VBA

⧭ Output:

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

Read More: Excel Delete Rows in a Range with VBA (3 Easy Ways)


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

VBA Code to Delete Hidden Rows in Excel VBA

⧭ Output:

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

Read More: How to Delete Row Using VBA (14 Ways)


Similar Readings:


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

VBA Code to Delete Hidden Rows in Excel VBA

⧭ 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: Macro to Delete Row in Excel If Cell is Blank


Examples to Delete 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

VBA Code to Delete Hidden Rows in Excel VBA

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

Input Box to Delete Hidden Rows in Excel VBA

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 Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)


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.

Opening a UserForm to Delete Hidden Rows in Excel VBA

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

Dragging Tools to Delete Hidden Rows in Excel VBA

⧪ 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

ListBox2 Code to Delete Hidden Rows in Excel VBA

⧪ 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

TextBox1 Code to Delete Hidden Rows in Excel VBA

⧪ 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

UserForm Code to Delete Hidden Rows in Excel VBA

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

UserForm Loaded to Delete Hidden Rows in Excel VBA

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.

Running UserForm to Delete Hidden Rows in Excel VBA

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.

Read More: How to Delete Rows in Excel: 7 Methods


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.


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

ExcelDemy
Logo