VBA filtering a column with variables

Question

I'm looking to filter a column between values but those values are variables within my spreadsheet. Whenever I use this it filters all of the values and nothing is displayed. I'm assuming it's something with the syntax between the ">=" and "<=" in combination with the variables upper_limit and lower_limit but I can't quite figure it out.

Sub Filter_QB_Cost()
'
' Filter_QB_Cost Macro
'
    Dim upper_limit As Integer
    Dim lower_limit As Integer
    
    upper_limit = Application.Workbooks("Fantasy FB.xlsm").Worksheets("QB Selection").Range("D6")
    lower_limit = Application.Workbooks("Fantasy FB.xlsm").Worksheets("QB Selection").Range("D7")
'

    ActiveSheet.ListObjects("Table11").Range.AutoFilter Field:=5, Criteria1:= _
        ">=lower_limit", Operator:=xlAnd, Criteria2:="<=upper_limit"
End Sub

">="&lower_limit note the use and location of the ampersand. — user3259118
– user3259118, Commented Nov 5, 2020 at 3:00

Ricardo Diaz · Accepted Answer · 2020-11-05 02:59:58Z

1

I haven't test it but you're missing some quotes and ampersands in order for the variables to be read.

Also some minor fixes with variable types and extra qualifiers not needed.

Sub Filter_QB_Cost()
'
' Filter_QB_Cost Macro
'
    Dim upper_limit As Long
    Dim lower_limit As Long
    
    upper_limit = Workbooks("Fantasy FB.xlsm").Worksheets("QB Selection").Range("D6").Value
    lower_limit = Workbooks("Fantasy FB.xlsm").Worksheets("QB Selection").Range("D7").Value
'

    ListObjects("Table11").Range.AutoFilter Field:=5, Criteria1:= _
        ">=" & lower_limit & "", Operator:=xlAnd, Criteria2:="<=" & upper_limit
End Sub

Let me know if it works

answered Nov 5, 2020 at 2:59

Ricardo Diaz

5,7262 gold badges24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeff A Over a year ago

Nailed it. Thank you, sir!

avarice · Accepted Answer · 2020-11-05 03:01:42Z

0

You can use & operator to concatenate strings and variables:

ActiveSheet.ListObjects("Table11").Range.AutoFilter Field:=5, Criteria1:= _
        ">="&lower_limit&"", Operator:=xlAnd, Criteria2:="<="&upper_limit

answered Nov 5, 2020 at 3:01

avarice

15.6k3 gold badges19 silver badges38 bronze badges

Collectives™ on Stack Overflow

VBA filtering a column with variables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related