0

This is the code I have, but the line Rows("4:858").Select does not work well as the Filtered rows always varies. I think I need to use SpecialCells (I have a header), but I want to ensure that only the Filtered rows are deleted and I am just learning Visual Basic. I used CurrentRegion for filtering the data.

' Filters for location = blank or #N/A
ActiveSheet.Range("$A$3").CurrentRegion.AutoFilter Field:=5, Criteria1:="=#N/A", _
    Operator:=xlOr, Criteria2:="="

' Selects all rows and deletes; NEED TO REPLACE THIS WITH MORE FLEXIBLE CODE
Rows("4:858").Select
Selection.Delete Shift:=xlUp

' Unfilters Location
Range("E2").Select
ActiveSheet.Range("$A$3").CurrentRegion.AutoFilter Field:=5

Any help with this code or even simplifying it would be greatly appreciated.

GSerg
  • 76,472
  • 17
  • 159
  • 346
M Klein
  • 3
  • 3
  • If `ActiveSheet.Range("$A$3").CurrentRegion` works for you as the correct region to filter, then this is also the range to call `.Offset(1, 0)` on like shown in the duplicates (so that you don't need to count the number of rows as a separate step). – GSerg Jan 20 '21 at 20:49
  • Thank you. It worked. – M Klein Jan 21 '21 at 18:52

0 Answers0