CODEWITHSUNDEEP
arraysexcelvba
paczek
paczek

Reputation: 25

filtering based on array in VBA

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

Upvotes: 0

Views: 278

Answers (1)

FunThomas
FunThomas

Reputation: 29146

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.

Upvotes: 1

Related Questions