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