How to use ActiveSheet when sorting/filtering in VBA

[jardenp]

I'm trying to create a macro to, among other things, sort, filter, and delete rows. From the macro recorder, I get:

Range("A2").Select
    ActiveWorkbook.Worksheets("TXHLPFKP381540").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TXHLPFKP381540").sort.SortFields.Add Key:=Range( _
        "A2:A31369"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("TXHLPFKP381540").sort
        .SetRange Range("A2:A31369")
        .Header = xlGuess
        .MatchCase = False
        .orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$31369").AutoFilter Field:=1, Criteria1:="<>110*" _
        , Operator:=xlAnd, Criteria2:="<>111*"
    Range("A2").Select
    Selection.CurrentRegion.Select
    Selection.EntireRow.Delete
    Selection.AutoFilter
    Range("A1").Select

The reports I will run this on will have different sheet names and ranges, so I want to make the code non-sheet name and non-range specific. I suspect I would use "ActiveSheet" but I'm not sure where I would use it. Should I replace "Worksheets("TXHLPFKP381540")" with "ActiveSheet"? I tried that and it didn't work, but I probably didn't do it right.

As to the range, my normal attack is to replace the given range--here "Range("A2:A31369")"--with "Range("A2:A" & Range("A2").End(xlDown).Row)". Would this allow the macro to work on report with fewer or more rows than the example I'm using to create the macro (i.e., 31369 rows)?

Thanks for any help you can provide.

 

[VoG]

Try

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
With ActiveSheet.Sort
    .SetRange Range("A2:A" & LR)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Columns("A").AutoFilter
ActiveSheet.Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:="<>110*" _
    , Operator:=xlAnd, Criteria2:="<>111*"
Range("A2").CurrentRegion.EntireRow.Delete
ActiveSheet.AutoFilter

 

[jardenp]

Thanks VoG! Definitely not the first time you've helped me. Setting the extended range description as LR is a great idea. I use that repeatedly in my macros, so that will help in other projects too.

If I could ask one follow-up, the last line "Activesheet.Autofilter" was meant to remove the filter, leaving only the cells that started with "110" or "111", but it doesn't. Since my original macro didn't work, I never got to the end to notice it too didn't work. What is the VBA code for removing a filter?

Thanks again.

 

[VoG]

Try

Range("A1").AutoFilter

 

[jardenp]

Voila! Thanks!