Advanced Filter VBA Code

[bino1121]

Sub Advanced_Filter()

Dim Ws1 As Worksheet
Dim Ws2 As Worksheet

Dim dataSet As Long
Dim setCriteria As Long

Set Ws1 = Workbooks("Template Report.xlsx").Sheets(1)
Set Ws2 = Workbooks("Template Report.xlsx").Sheets(2)

dataSet = Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Row
setCriteria = Ws2.Range("B" & Ws2.Rows.Count).End(xlUp).Row

Range("dataSet").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("setCriteria"), Unique:=False


End Sub

I am trying to advanced filter Sheet 1 from A3 to xldown (variable range) by sheet 2 from B1 xldown (Variable range) The bold section is where I get the current error Run time error 1004 Method 'Range' of object_'Global' Failed

but the goal is to filter sheet1 in place from A3 to xldown by a list on sheet2 in the same workbook starting from B1 to xldown.

 

[Skyybot]

You've already defined dataSet and setCriteria as Ranges. No need for Range clarification. Try this.

dataSet.AdvancedFilter (Action:= xlFilterInPlace, CriteriaRangea:= setCriteria, Unique:= False)

 

[bino1121]

You've already defined dataSet and setCriteria as Ranges. No need for Range clarification. Try this.

dataSet.AdvancedFilter (Action:= xlFilterInPlace, CriteriaRangea:= setCriteria, Unique:= False)

New error trying that fix says invalid qualifier and highlights dataSet

 

[NoSparks]

@bino1121, you have no ranges set.
you've declared dataSet and setCriteria as Long. They are just the last row number of those columns.
Declare them as range and try

With Ws1
    Set dataSet = .Range("A3:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

With Ws2
    Set setCriteria = .Range("B1:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
End With

 

[bino1121]

@bino1121, you have no ranges set.
you've declared dataSet and setCriteria as Long. They are just the last row number of those columns.
Declare them as range and try

With Ws1
    Set dataSet = .Range("A3:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

With Ws2
    Set setCriteria = .Range("B1:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
End With

You're my hero Thank you so much!!!!!!!

 

[Skyybot]

@bino1121, you have no ranges set.
you've declared dataSet and setCriteria as Long. They are just the last row number of those columns.
Declare them as range and try

With Ws1
    Set dataSet = .Range("A3:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

With Ws2
    Set setCriteria = .Range("B1:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
End With

Nice catch @NoSparks . I didn't even notice that.

 

[NoSparks]

You're welcome, glad I could assist.