VBA To Select All Visible Rows After Autofilter

[honkin]

Hi

I recorded a macro of running an autofilter and then selecting all the visible rows and deleting. Here is the code it returned:

Selection.AutoFilter
    ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=21, Criteria1:="500"
    ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=21, Criteria1:="=500" _
        , Operator:=xlOr, Criteria2:="="
    Rows("51:51").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp

It initially put a range in the code, but I have now resolved that issue of the variable number of rows in the daily file downloaded from a website, but I need the selection phase to select all visible rows except the header row and then delete. The rows will, of course, be non-contiguous, so that will be the issue

cheers

 

[honkin]

Hi Peter_SSs

I had a go at fixing all of that myself and it appears to work. Does anything in this look not quite right?

Range("A1").CurrentRegion.Select
    With Selection
      .AutoFilter Field:=21, Criteria1:="=500", Operator:=xlOr, Criteria2:="="
      .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
      .AutoFilter
    End With
    Range("A1").CurrentRegion.Select
    With Selection
      .AutoFilter Field:=17, Criteria1:= _
        "#DIV/0!"
      .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
      .AutoFilter
    End With
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "A2:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
        "B2:B" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
        "U2:U" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A2:U" & LR)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Cheers

 

[Peter_SSs]

Nothing looks obviously wrong and if it is working ...

 

[honkin]

Excellent mate. I just know that often the macro recorder puts things in a way that can be streamlined, but as you say, if it is working

Thanks so much for your help on the autofilter section

cheers