How to pass a variable to a range? (VBA)

user_10604800 0
Viewed 489

I'm creating a Macro (Excel)

I'm saving the amount of rows used on the sheet via a variable and I need to pass it to a filter range, hope this code helps to explain what I'm trying to do...

Sub Filtering()

Rows("11:11").Select
        Selection.AutoFilter

     'save the amount of rows used on the sheet
    Dim lastRow As Long
        lastRow = Range("AC" & Rows.Count).End(xlUp).Row

    ActiveSheet.Range("$A$11:$AC$lastRow").AutoFilter Field:=18, Criteria1:=">10", _
            Operator:=xlAnd

    End Sub

The filter will always be on row 11 and columns will always be from A to AC but the number of rows is different on each Excel file. can you guys help? pls

vba excel
1 Answers

You are very close. Similar approach to when you defined your range for the last row. The variable has to be outside the " "

Option Explicit

Sub Filtering()

    'Rows("11:11").Select 'Redudant
    'Selection.AutoFilter 'Redudant

    'save the amount of rows used on the sheet
    Dim lastRow As Long
    lastRow = Range("AC" & Rows.Count).End(xlUp).Row

    ActiveSheet.Range("$A$11:$AC" & lastRow).AutoFilter Field:=18, Criteria1:=">10", _
        Operator:=xlAnd

End Sub

Result

enter image description here

user_45525290
Related