Rifat Hassan
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
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.
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 iSo, 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: 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
⧭ 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:
- How to Delete Selected Rows with Excel VBA (A Step-by-Step Guideline)
- Delete Unused Rows in Excel (8 Easy Ways)
- How to Delete Hidden Rows in Excel (3 Methods)
- Delete Filtered Rows in Excel (5 Methods)
- How to Delete Every Other Row In Excel (4 Methods)
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: 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
⧭ 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 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.
⧪ 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.
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.
