Excel VBA to filter based on range

Question

All,

I'm trying to filter items on a sheet based on a range of data that gets input from another sheet. My current macro is only filtering based on the 1st cell in that range.

The range I'd like it to filter based on will change day to day. Some days it may be 3 items some days it may be 7. So I am not able to just have the filter look at 4 specific cells.

Here is what I currently have and I am not sure why it isn't filtering based off all 4 cells, instead it is only filtering based on the 1st cell in that range.

Dim POinput As Range, cell As Range
Dim lrow As Integer
Dim arrList() As String, lngcnt As Long

With Sheets("Control")
    lrow = Range("A50").End(xlUp).Row
    Set POinput = .Range("A5:A" & lrow)
End With

lngcnt = 0
For Each cell In POinput
    ReDim Preserve arrList(lngcnt)
    arrList(lngcnt) = cell.Text
    lngcnt = lngcnt + 1
Next


Sheets("ACTOpenItems").Select
    ActiveSheet.Range("A1:FH" & lastrow).AutoFilter Field:=2, Criteria1:=arrList, _
                                                    Operator:=xlFilterValues

For example assume I've entered the data in "Control" tab of 1 and 3 because these are the items I want to filter on in column B of the "ACTOpenItems" tab.

The ACTOpenItems tab looks like this.

Type / Acct / Amount

Dog / 1 / 100

Cat / 2 / 150

Bird / 3 / 200

Dog / 4 / 250

Cat / 1 / 300

Bird / 2 / 350

Dog / 3 / 400

Cat / 4 / 450

Bird / 1 / 500

Dog / 2 / 550

Cat / 3 / 600

Bird / 4 / 650

And I want the results of.

Type / Acct / Amount

Dog / 1 / 100

Bird / 3 / 200

Cat / 1 / 300

Dog / 3 / 400

Bird / 1 / 500

Cat / 3 / 600

Thank you in advance for any help.

The tab "ACTOpenItems" has a ton of data. I want to only look at rows correlating to data that got input on the "Control" tab. So if the person running the macro inputs account numbers 1, 3, 7, and 9 today I want the macro to filter "ACTOpenItems" tab by those 4 accounts in column B. Tomorrow those accounts may be different. So I can't just have it always filter by 1, 3, 7 and 9 — Jasonfish11
So you won't have to deal with that stuff: ReDim arrList(POinput.Cells.Count). ReDim Preserve never seems to work as intended for me. — user3819867
Adding a filter works perfectly. That is how we are currently doing it. But that is just a step in the process. The process we have currently is taking someone 30 minutes each day. I have the macro working (sans the filter part) in about 3 seconds. The filter is midway through the process. I could always have 2 macros after the 1st one runs have someone manually set up the filter then run the 2nd one. But I thought that I could have the macro auto filter based on a range of data. — Jasonfish11

Jasonfish11 Jasonfish11 · Accepted Answer · 2015-06-10T18:11:09

I've found the error. Made a slight mistake in the code. Should have had a "." before my Range. It is fixed now. Just wanted to post the update in case anyone wants to do the same thing.

This

With Sheets("Control")
    lrow = Range("A50").End(xlUp).Row

Should have been this

With Sheets("Control")
    lrow = .Range("A50").End(xlUp).Row

Excel VBA to filter based on range

1 Answers