CODEWITHSUNDEEP
excelvba
reggie86
reggie86

Reputation: 289

VBA Autofilter with variable range

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

Upvotes: 1

Views: 2658

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

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

Upvotes: 2

Related Questions