12

I guess this is pretty straight forward, but for some reason it just does not seem to work for me :(

I have the below code which auto-filters the data based on the criteria that I have specified:

Dim lastrow As Long
lastrow = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row

With Sheet2
    .AutoFilterMode = False

    With .Range("A1:AF" & lastrow)
    .AutoFilter
    .AutoFilter Field:=7, Criteria1:="Yes", Operator:=xlFilterValues

    End With

What I am now looking to do is delete all the Unfiltered (Hidden) rows that do not fit the criteria.

I tried so far:

Sub RemoveHiddenRows 
Dim oRow As Object 
For Each oRow In Sheets("Sheet2").Rows 
If oRow.Hidden Then oRow.Delete 
Next 
End Sub

But the problem with this code is that it would only remove every other row of consecutive hidden rows because the each increments the row considered even when a row has been deleted and all lower rows have moved up one.

Also I would prefer something without a loop if it's possible, kind of like the opposite of .SpecialCells(xlCellTypeVisible).EntireRow.Delete

All help will be highly appreciated.

CaptainABC
  • 1,229
  • 6
  • 24
  • 40
  • 1
    why not just change criteria to `Criteria1:="<>Yes"` and then `.SpecialCells(xlCellTypeVisible).EntireRow.Delete`? – Dmitry Pavliv Mar 08 '14 at 21:40
  • @simoco In this case it would work, but in other scenarios I filter the data based on 3 or 4 multiple criteria, sometimes with each criteria carrying more than 1 value. So I was kind of looking to get rid of Unfiltered Data rather than trying to reverse all the criteria and delete the visible cells. Any suggestions? – CaptainABC Mar 08 '14 at 21:43
  • 1
    Just loop over the rows and check if `.Hidden = True`, then delete those that are. If you don't want a delete loop, do a `Union` loop of the hidden rows, then delete them in one go. – WGS Mar 08 '14 at 21:51

2 Answers2

22

So I was kind of looking to get rid of Unfiltered Data rather than trying to reverse all the criteria and delete the visible cells

I would use this one:

Sub RemoveHiddenRows()
    Dim oRow As Range, rng As Range
    Dim myRows As Range
    With Sheets("Sheet3")
        Set myRows = Intersect(.Range("A:A").EntireRow, .UsedRange)
        If myRows Is Nothing Then Exit Sub
    End With

    For Each oRow In myRows.Columns(1).Cells
        If oRow.EntireRow.Hidden Then
            If rng Is Nothing Then
                Set rng = oRow
            Else
                Set rng = Union(rng, oRow)
            End If
        End If
    Next

    If Not rng Is Nothing Then rng.EntireRow.Delete
End Sub
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • I copy pasted it and got an error, but fixing it only required replacing `Sheets("Sheet3")` With `ActiveSheet`. Works like a charm! – Calculus Knight Nov 29 '16 at 15:48
2

I used Dmitry Pavliv's solution for my filtered table and it worked (thanks!) but would intermittently give error: "delete method of range class failed" error.

Error seemed to occur when only one hidden row was to be deleted. It may or may not be of significance that the lone hidden row was right under the table header.

Stepping through the code, rng pointed to correct cell, and showed just the single cell. It was probably an issue with using a Table instead of named range, though other hidden rows deleted fine in same table format.

Macro has been working fine after I modified the last portion of the code from this:

If Not rng Is Nothing Then rng.EntireRow.Delete

To this: 

If rng.Rows.Count = 1 Then
   ws.Rows(rng.Row & ":" & rng.Row).Delete
ElseIf rng Is Nothing Then
   rng.EntireRow.Delete
End If

For some reason, deleting that single row in this format works. I'm not quite sure why. The rng object is pointing to the correct cell and I'm using it to get the row number, so not sure why it's not working in rng.entirerow.delete statement. Oh well. Sharing as came across many posts with same error unresolved.

Qudsia
  • 53
  • 8