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

 

[footoo]

You can get the code from the macro recorder (via SpecialCells>Visible cells only).

 

[Peter_SSs]

There is no need to select or even refer to the visible cells to delete them.
In fact there is generally no need to select anything and doing so slows your code.

I'm not sure what, if anything, row 51 has to do with what you are trying to achieve, but try this in a copy of your workbook.

With Range("A1").CurrentRegion
  .AutoFilter Field:=21, Criteria1:="=500", Operator:=xlOr, Criteria2:="="
  .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
  .AutoFilter
End With

 

[honkin]

Cheers foootoo, but have absolutely no idea where such a menu item exists in Excel for Mac 2019. I will try searching, but the usual suggestions of Edit and Go To to get to Special Cells section have not worked
ta!

 

[honkin]

cheers Peter SSs

That one falls over on the .Autofilter line saying run-time error 1004 Method 'Autofilter' of object 'Range' failed.

Any thoughts?

 

[Peter_SSs]

That one falls over on the .Autofilter line

The first AutoFilter line or the second?

Does your data on the active sheet start in cell A1 and extend (without vacant columns) as far as column U at least?

 

[Peter_SSs]

That one falls over on the .Autofilter line saying run-time error 1004 Method 'Autofilter' of object 'Range' failed.

Actually, I missed that you are working on a Mac so what I said about selection may not apply. I don't use a Mac so I'm not sure about that.
What happens with this?

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

 

[honkin]

Now you've nailed it.

In answer to your earlier question, the data begins in A2, with headers taking up the first row. The last column is about CM or such

That looks to have done the trick though, so thanks very much

cheers

 

[Peter_SSs]

That looks to have done the trick though, so thanks very much

Good news! You're welcome.

 

[honkin]

A quick question if it is not stretching the friendship; what I have is a pretty long code to prepare daily downloaded files for use with filters down the track. The files just need to have a particular look.

The earlier code is not showing here as it all works fine, though may be a little bloated due to coming directly from the macro recorder, but some of the rest still has ranges which will limit the usefulness of the code, as files will come in sometimes with 7,000 rows, sometimes 10,000 and sometimes only 4,000. I just need any of the ranges to be generic to handle all sizes. The earlier autofilter parts are perfect, but the next sections after that hopefully selects all data and sorts it based on data in 3 columns A, B + U. Again the ranges are not right; "A2:A6428" needs to encompass the entire column. Do I just make those sorts of references like this "A:A"? And the line

Range("G11").Activate

I have no idea what it does, as G11 does not need to do anything. Is it maybe not referring to G11 but rather an option in the Data>Sort menu?

Would you possibly mind just passing an eye over this to see what you think?

Thanks in advance mate

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
    Selection.AutoFilter
   Range("A1").CurrentRegion.Select
    Range("G11").Activate
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
        "A2:A6428"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
        "B2:B6428"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
        "U2:U6428"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1:CO6428")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With