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
– Scott Craner, Commented Jan 20, 2017 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
– YowE3K, Commented Jan 20, 2017 at 16:33
YowE3K, that solved it! Could you post that as an answer so I can give you the credit? Thank you! — DeNaeL
– DeNaeL, Commented Jan 20, 2017 at 16:42

YowE3K · Accepted Answer · 2017-01-20 16:50:34Z

3

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).

answered Jan 20, 2017 at 16:50

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related