VBA AutoFilter with array Criteria

Topic

New Reply

The AutoFilter in the snip below does not select any rows. arArray is defined by Dim arArray() as Long. The previous step loads the array (1) to (3) with three valid long values (checked by locals view.) If I remove the Operator:=xlFilterValues the row with the matching value for (3) is selected as described for AutoFilter. This project has other single criteria AutoFilters working fine but with strings

Help me see what I’m doing wrong. Using Office 2010 32 bit version.

Code:

'----------- Step 3: Autofilter to display items matching the Bar's in the array.

Worksheets("Sheet1").Activate
Sheet1.Range(sRng).AutoFilter
Sheet1.UsedRange.AutoFilter Field:=iBar, Criteria1:=arArray, Operator:=xlFilterValues
Sheet1.Cells.Sort Key1:=Range(sAut), key2:=Range(sSrt), Order1:=xlAscending, _
    Header:=xlYes, MatchCase:=False, Orientation:=xlSortColumns
Display:

Thanks, Buford

Reply | Quote

Replies

You need to declare the array as String even if the data is numeric.

Reply | Quote

You need to declare the array as String even if the data is numeric.

Ok Rory, you got me confused.:confused: Could you please elaborate…inquiring minds want to know…and so do I! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks a lot. Works OK.

Buford

Reply | Quote

Sure.

Even if you want to filter for say the values 1, 2, 3 and 4 the array you pass to the autofilter criteria actually needs to contain “1”, “2”, “3” and “4”. The simple way to do that is to declare your array variable as a String rather than Numeric type so that whatever you populate it with ends up as text.

Reply | Quote