VBA Filter range by more than 1 criteria using cell reference

[floggingmolly]

I have a worksheet with data in A3 - I 1400. Column A contains Station #'s. I want the user to be able to enter multiple station #'s in Row 2 (B2, C2, D2) and filter the data by those stations. If I use an array in the VBA it filters by the station #'s, but I want it to reference cells B2, C2, D2, etc. Is anyone able to assist? Below is the code I have with station #'s as an array but this obviously isn't what I need. I need the Criteria to be a range I'm guessing to reference cells? I can't figure it out. Any help would be greatly appreciated.

Sub Filter()
Sheet1.Range("A3:I3").AutoFilter Field:=1, _
                                 Criteria1:=Array("11", "16"), _
                                 Operator:=xlFilterValues
End Sub

 

[mumps]

Try:

Sub Filter()
    Dim arr As Variant, lCol As Long
    lCol = Cells(2, Columns.Count).End(xlToLeft).Column - 1
    arr = Range("B2").Resize(, lCol).Value
    Sheet1.Range("A3").AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
End Sub

 

[floggingmolly]

Doesn't seem to work. It filters the entire sheet and nothing is visible. My goal is so they can enter up to 3 station numbers in B2, C2, and D2 and filter the data by up to 3 stations. I can get it to filter by 1 station using this code below. Not sure how to add in C2 and D2.

Sub Filter()
Sheet1.Range("A3:I3").AutoFilter Field:=1, Criteria1:=Range("B2").Value
End Sub

Try:

Sub Filter()
    Dim arr As Variant, lCol As Long
    lCol = Cells(2, Columns.Count).End(xlToLeft).Column - 1
    arr = Range("B2").Resize(, lCol).Value
    Sheet1.Range("A3").AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
End Sub

 

[mumps]

The code I suggested worked on some dummy data. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[floggingmolly]

The code I suggested worked on some dummy data. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

I don't have dropbox and I tried the XL2BB add-in a few times and can't figure it out. Really all I want is in cells B2, C2, and D2 the user can enter a station number. I want the data to filter according what to what station #'s they enter. So if they put 11, 24, and 40 in cells B2, C2, and D2 I want the data to filter by those stations which are listed in column A. My code will work for one cell, B2, but not sure how to add more so it looks at B2, C2, and D2.

Sub Filter()
Sheet1.Range("A3:I3").AutoFilter Field:=1, Criteria1:=Range("B2").Value
End Sub

 

[Fluff]

How about

Sub Filter()
Sheet1.Range("A3:I3").AutoFilter 1, Range("B2:D2").Value, xlFilterValues
End Sub

 

[floggingmolly]

How about

Sub Filter()
Sheet1.Range("A3:I3").AutoFilter 1, Range("B2:D2").Value, xlFilterValues
End Sub

I tried and that filtered out everything so no rows were showing. I tried this and it worked with 2 criteria, but if I try to add the 3rd, it doesn't work. I just found where you can only use 2 with Operator:=xlOr so I guess that idea is out. haha

Sub Filter()
Sheet1.Range("A3:I3").AutoFilter Field:=1, Criteria1:=Range("B2").Value, _
Operator:=xlOr, Criteria2:=Range("C2").Value
End Sub

 

[Fluff]

You can only have more than 2 criteria by using an array.
Make sure that B2:D2 are text values & not numbers.

 

[floggingmolly]

You can only have more than 2 criteria by using an array.
Make sure that B2:D2 are text values & not numbers.

ahhhh, I changed them to Text and it looks like your code might actually work. I wasn't even thinking about the formatting. I think this might give me what I need. I appreciate the time you took to help me. This should get me started on this project so I am very thankful.

 

[Fluff]

Glad we could help & thanks for the feedback.