Excel VBA - Use array as criteria in autofilter, not expected results

Question

The ultimate goal of this procedure is to delete all rows in the "TestData" table that have a user which is listed in the "SysAccts" table (on the "Lists" worksheet).

I'm using autofilter to find those rows so I can select them and delete them.

This is my code:

Sub ClearSystemAccts()
    Dim wksData As Worksheet
    Dim objDataTable As ListObject
    Dim varSysAccts As Variant

    Set wksData = ActiveWorkbook.Sheets("Test Data")
    Set objDataTable = wksData.ListObjects("TestData")
    varSysAccts = Worksheets("Lists").Range("SystemAccts")

    With objDataTable.Range
        'turn off the autofilters as a reset
        .AutoFilter
        'set the autofilter with the criteria;
        .AutoFilter field:=2, Criteria1:=varSysAccts, Operator:=xlFilterValues
        'Delete the entire row for the visible cells; _
        'skips the header but also deletes the blank row below the table.
        .CurrentRegion.Offset(1).EntireRow.Delete
        'turn off autofilter
        .AutoFilter
    End With

End Sub

When I run the code, the autofilter display no rows in the table.

I have validated that the varSysAccts variable is properly populated.

When I hover over the criteria in the Autofilter line, I get the following:

I think the problem is that when a table is read into an array, it is always a dynamic array. But the table is only one column. How do I fix this?

EDITED: Per the comment below, I removed the array from the criteria. Now I don't get the type mismatch, but the filter still doesn't select any rows.

Try without the Array() wrapper as it already is an array. Just use varSysAccts — Scott Craner, Jan 20 '17 at 16:19
Assuming that `SystemAccts` is a column, try `varSysAccts = Application.Transpose(Worksheets("Lists").Range("SystemAccts"))` (to make the array a one-dimensional array) and then `Criteria1:=varSysAccts`. — YowE3K, Jan 20 '17 at 16:33
YowE3K, that solved it! Could you post that as an answer so I can give you the credit? Thank you! — DeNaeL, Jan 20 '17 at 16:42

score 3 · Accepted Answer · answered Jan 20 '17 at 16:50

Assuming that SystemAccts is a column, use

varSysAccts = Application.Transpose(Worksheets("Lists").Range("SystemAccts‌"))

That will make the array one-dimensional, rather than two-dimensional (rows x columns).

And then, because you already have an array, you only need to use Criteria1:=varSysAccts instead of Criteria1:=Array(varSysAccts).

Excel VBA - Use array as criteria in autofilter, not expected results

1 Answers1