0

I would like to make a code which would filter out the Purchase Order (6-digit numbers) numbers based on a certain range of these numbers.

I have 2 sheets in my workbook. The first one, called "Start", includes the numbers in range A1:A4, namely 180029, 180298, 181073, 190152. On the second tab, called PO list, I have a range of information - 15 columns, 23 rows. One of the columns of the PO list contains several PO numbers, but I would like to filter out only the ones that are in the range A1:A4 - which I considered as an array.

Sub auto_filter()
    Dim my_array() As Variant
    my_array = Sheets("Start").Range("A1:A4").Value

    Sheets("PO list").Range("A1").AutoFilter Field:=7, Criteria1:=my_array
    'Operator:=xlFilterValues

I expect to have the result that the PO numbers from range A1:A4 will be filtered out on the tab PO list. The code, that I wrote, only filters out the the PO number 190152 - the other three are omitted.

GSerg
  • 76,472
  • 17
  • 159
  • 346
Peter Katocs
  • 9
  • 1
  • Note that you need both to ensure that your cells contain strings and not numbers (as explained in the [accepted answer](https://stackoverflow.com/a/42932732/11683)), and to use `Transpose` because you are building the list [from a column of cells](https://stackoverflow.com/questions/42932353/filter-using-array-and-xlfiltervalues#comment72963362_42932508). If your list contains numbers, either turn them into strings or build the filter array manually with `CStr`. – GSerg Jun 27 '19 at 13:50

1 Answers1

0

The problem that you will run into here is to do with Dimensions - the Range.Autofilter Method wants a 1D array, but you are passing it a 1-by-4 2D array

Dim RangeArray() As Variant, FilterArray() As Variant, ArrayPointer As Long
RangeArray = ThisWorkbook.Worksheets("Start").Range("A1:A4").Value

'Copy to a Base0 1D Array
ReDim FilterArray(1 To uBound(RangeArray,1)-lBound(RangeArray,1))

For ArrayPointer = 0 To uBound(FilterArray)
    FilterArray(ArrayPointer) = RangeArray(ArrayPointer+lBound(RangeArray,1), lBound(RangeArray,2))
Next ArrayPointer

'Use the 1D Array
ThisWorkbook.Worksheets("PO List").Range("A1").Autofilter Field:=7, Criteria1:=FilterArray, Operator:=xlFilterValues
Chronocidal
  • 6,827
  • 1
  • 12
  • 26
  • 1
    It is much easier to [use `Transpose`](https://stackoverflow.com/a/42932508/11683) for that. – GSerg Jun 27 '19 at 13:49