Use Variant Variable In Filter Condition In VBA

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    I am wanting to use VBA to filter a worksheet. I am reading the userID's from the worksheet like this

    Code
    Dim userID As Variant
Dim uID As Variant
userID = Sheets("Data").Range("B2:B101").Value


    Then I try to do the below, to use VBA to filter it filters out all rows acting as if the value does not exist?

    Code
    For Each uID In userID
    Sheets("Data").Range("$A$1:$BV$153").AutoFilter Field:=2, Criteria1:=userID
Next

    You are setting userid to a range of cells, it needs to be set to one cell. I'm not sure why you are looping but maybe you mean


    Code
    For Each uID In userID     Sheets("Data").Range("$A$1:$BV$153").AutoFilter Field:=2, Criteria1:=uID Next

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login