CODEWITHSUNDEEP
excelvba
Naqi Kazmi
Naqi Kazmi

Reputation: 23

Deleting Hidden Rows in a Selected Range

I want to delete all the hidden rows from a selected range. Here selected range is the range which user selects through the input box. In my loop I am moving from LastRow to StartRow. I am facing problem defining the rows as I want the the StartRow to be the first row of that selected range and LastRow to be last row at the end of selected range's region The code is giving a mismatch error '13'.

I am new to VBA and probably making some stupid mistake in the code below.

Sub Delete_Hidden_Row()
Dim LastRow As Long
Dim StartRow As Long
Dim r As Long
Dim MyRange As range

Set MyRange = Application.InputBox(Prompt:="Select the first Cell (Hidden Rows in the region of the 
selected cell will be deleted) ", _
Title:="Delete Hidden Rows", Type:=8)

StartRow = range(MyRange.Rows, MyRange.Columns).Rows.EntireRow
LastRow = range(MyRange.Rows, MyRange.Columns).CurrentRegion.Rows.Count + 1
For r = LastRow To StartRow
    If Rows(r).Hidden = True Then
        Rows(r).Delete
        
    End If
Next r

Upvotes: 0

Views: 129

Answers (2)

NickSlash
NickSlash

Reputation: 5077

When deleting rows you need to adjust your loop slightly. If you delete the first row the second row becomes the first row and your upper limit becomes too big and so you get an error.

Easiest method to fix it is loop backwards, start from the last row and work backward.

Another method is to not increase your row counter until it encounters a visible row and moving on to the next.

Update

Below are examples of the methods I mentioned. When working with ranges the row is relative to the range, so it always goes from 1 to Row Count.

Sub Delete_Hidden_One()
Dim TargetRange As Range
Set TargetRange = Application.InputBox(Prompt:="Select the first Cell (Hidden Rows in the region of the selected cell will be deleted) ", Title:="Delete Hidden Rows", Type:=8)
Dim Row As Integer
Dim TargetStart As Integer
TargetStart = TargetRange.Rows.Count
For Row = TargetStart To 1 Step -1
    If TargetRange.Rows(Row).EntireRow.Hidden = True Then
        Debug.Print "Deleting Row <" & Row & ">"
        TargetRange.Rows(Row).EntireRow.Delete xlShiftUp
    Else
        Debug.Print "Skipping Row <" & Row & ">"
    End If
Next Row
End Sub

Sub Delete_Hidden_Two()
Dim TargetRange As Range
Set TargetRange = Application.InputBox(Prompt:="Select the first Cell (Hidden Rows in the region of the selected cell will be deleted) ", Title:="Delete Hidden Rows", Type:=8)
Dim Row As Integer
Row = 1
Do
    If TargetRange.Rows(Row).EntireRow.Hidden = True Then
        Debug.Print "Row <" & Row & "> Is Hidden - Deleting"
        TargetRange.Rows(Row).EntireRow.Delete xlShiftUp
    Else
        If Row < TargetRange.Rows.Count Then
            Debug.Print "Row <" & Row & "> Is Not Hidden - Incrementing"
            Row = Row + 1
        Else
            Debug.Print "Row <" & Row & "> Is Out-Of-Bounds - Exiting"
            Exit Do
        End If
    End If
Loop
End Sub

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27249

This should get you there:

StartRow = MyRange.Cells(1,1).Row
LastRow = MyRange.Cells(myRange.Rows.Count,1).Row

For r = LastRow To StartRow
    If r.EntireRow.Hidden Then 
        Dim remove as Range
        If remove Is Nothing Then
            Set remove = r
        Else
            Set remove = Union(remove,r)
        End If        
    End If
Next r

remove.EntireRow.Delete

Upvotes: 0

Related Questions