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.
Once the data is filtered, you can delete only the filtered rows by selecting the filtered data and pressing the "Ctrl" and "-" keys on your keyboard at the same time. This will open the "Delete" dialog box. Make sure that the "Shift cells up" option is selected and then click the "OK" button.
To delete an entire row in Excel using VBA, you need to use the EntireRow. Delete method. The above code first specifies the row that needs to be deleted (which is done by specifying the number in bracket) and then uses the EntireRow. Delete method to delete it.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With