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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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

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
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.Label2.Visible = False
UserForm1.TextBox1.Visible = False

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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF