filtering based on array in VBA

Question

The below code worked 10 minutes ago and now I get error 1004: Autofilter method of Range class failed. Any idea why? I want to filter column C based on letter J - so filter only the values starting with J. After,I want to delete the hidden rows, but this doesn’t seem to work.please help

Option Explicit

Dim ws As Worksheet
Dim filterarray As Variant
Dim findarray As Variant
Dim MyLastRow1 As Long, i As Long, j As Long, k As Long

Sub test23()
    Set ws = ThisWorkbook.Worksheets("T&E Report")
    MyLastRow1 = Range("C100000").End(xlUp).Row
    findarray = Array("J")
    ReDim filterarray(0 To 0)

    j = 0

    For k = 0 To UBound(findarray)
        For i = 2 To MyLastRow1
            If InStr(ws.Cells(i, 3).Value, findarray(k)) > 0 Then
                filterarray(j) = ws.Cells(i, 3).Value
                j = j + 1
                ReDim Preserve filterarray(0 To j)
            End If 
        Next i
    Next k

    ws.Range("$C$1:$C$" & MyLastRow1).AutoFilter Field:=3, Criteria1:=Array(filterarray), Operator:=xlFilterValues
End sub

First, your Dim statements need to be within your Sub. If they are global you need to use Public to declare. — DDV
– DDV, Commented Jul 18, 2019 at 12:23
@Dean: You are right, all the variables should be declared within the Sub so that they are local. When you declare them at top of your code with Dim, they are global but only known within the module. If you declare them as Global, they are visible to all modules of your project. — FunThomas
– FunThomas, Commented Jul 18, 2019 at 12:30
You don't need the loop at all. Just filter using "J*" as the criterion. — Rory
– Rory, Commented Jul 18, 2019 at 12:47
@FunThomas thanks for that - was not aware that was the case! — DDV
– DDV, Commented Jul 18, 2019 at 13:23

FunThomas · Accepted Answer · 2019-07-18 12:48:29Z

1

You set your range of data to be filtered just to one column ("C"), but ask AutoFilter to set the filter according to the 3rd column of that range (so that would be column "E")

As column "E" is not part if the range to be filtered, you will get the 1004 runtime error. However, if you had already autofilter active that included column "E" (that means you have this triangle button visible on column E), the runtime error is not issued - maybe that's the reason the code worked 10 min ago.

answered Jul 18, 2019 at 12:48

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

filtering based on array in VBA

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