VBA Autofilter with variable range

Question

I am trying to make my autofilter range based on the value of variables, but it isn't working. Does anyone have any suggestions?

                   Dim y, z As Integer
                   y = 5
                   z = 3

                    rows(z).Select
                    Selection.AutoFilter
                    ActiveWorkbook.Worksheets("Active Worksheet").AutoFilter.Sort.SortFields.Clear
                    **ActiveWorkbook.Worksheets("Active Worksheet").AutoFilter.Sort.SortFields.Add _
                    Key:=Range(Cells(z, y)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
                    :=xlSortNormal**
                    With ActiveWorkbook.Worksheets("Active Worksheet").AutoFilter.Sort
                        .Header = xlYes
                        .MatchCase = False
                        .Orientation = xlTopToBottom
                        .SortMethod = xlPinYin
                        .Apply
                    End With

The error is coming up in the portion surrounded by asterisks--thanks! — reggie86, Jul 08 '16 at 17:13
For the Key, try `Worksheets("Active Worksheet").Cells(z,y)` (you need to declare what worksheet the `Range` and `Cells` is to be on). You can't (AFAIK) do ` Range(Cells())`, you need either `Cells()`, or `Range(Cells(),Cells())`. — BruceWayne, Jul 08 '16 at 17:19
Awesome! I used `Worksheets("Active Worksheet").Cells(z,y)` and that did the trick-- thanks! — reggie86, Jul 08 '16 at 17:28

score 2 · Accepted Answer · edited May 23 '17 at 11:52

Your main issue is you're using Range(Cells()), which isn't how VBA likes things. If using a range, you want to do either Range("A1"), or Cells(1,1). If you need to use Range, then you just do Range(Cells(1,1),Cells(1,1)).

So, in your erroring line, change the key to Key:=Cells(z,y) or Range(Cells(z,y),Cells(z,y)).

However, I also highly recommend avoiding using .Select/.Active:

Sub whatever()
Dim y As Integer, z As Integer
Dim mainWB  As Workbook
Dim activeWS As Worksheet

Set mainWB = ActiveWorkbook
Set activeWS = mainWB.Worksheets("Active Worksheet")

y = 5
z = 3

With activeWS
    .Rows(z).AutoFilter
    .AutoFilter.Sort.SortFields.Clear
    .AutoFilter.Sort.SortFields.Add Key:=.Cells(z, y), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub

VBA Autofilter with variable range

1 Answers1