Advanced Filter Help

[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.

 

[Lutfor Rahman Shimanto]

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.

Hello Bino1121

Thanks for reaching out and describing your problem with such clarity. You are right about the bold section of your given codes, which raises an error while running. Because you're not properly defining the ranges you want to use in the AdvancedFilter method.

I am delighted to inform you that I have modified the code you gave, and it works perfectly. This code will filter the data in Sheet1 (from A3 to the last used row) based on the criteria in Sheet2 (from B1 to the last used row) of the same workbook.

Excel VBA Code:

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

    Ws1.Range("A3:A" & dataSet).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Ws2.Range("B1:B" & setCriteria), Unique:=False

End Sub

I am attaching the two solution workbooks to help you understand better. the .xlsm file will contain the sub-procedure, and the .xlsx file will contain the data. While running the sub-procedure, keep both of the workbooks open. Good luck!

Regards
Lutfor Rahman Shimanto