CODEWITHSUNDEEP
vbaexcel
Danny Hagstrom
Danny Hagstrom

Reputation: 23

Filter for Multiple Criteria from a given range

I built a Pivot table to give me some product codes and other information that i need to look at following the product codes

What i am trying to do is Enter about 20 Product Codes into a given range. Run a VBA macro that will then filter A1 for the values that i entered in the Range. I have a code available but it is just not working correctly.

I have gotten the error Type Mismatch

    Sub ArrayAutofilterFromNamedRange()

    Dim oWS As Worksheet
    Dim oRange As Range
    Dim numrows As Integer
    Dim i As Integer
    Dim arCriteria(0 To 100) As String

    On Error GoTo Err_Filter

    Set oWS = ActiveSheet
    Set oRange = ActiveWorkbook.ActiveSheet.Range("mydynamicrange")
    numrows = oRange.Rows.Count
    i = 0

    For Each Row In oRange
    arCriteria(i) = Row.Value
    i = i + 1
    Debug.Print arCriteria(i)
    Next Row

    oWS.Range("A1").AutoFilter Field:=1, Criteria1:=arCriteria,                         
    Operator:=xlFilterValues


    Finish:
    If Not oWS Is Nothing Then Set oWS = Nothing


    Err_Filter:
    If Err <> 0 Then
    MsgBox Err.Description
    Err.Clear
    GoTo Finish
    End If

    End Sub

Upvotes: 0

Views: 54

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Your code line:

For Each Row In oange

will fail because oange has not been defined.

(there may ne other problems)

Upvotes: 1

Related Questions