Autofilter and set field using variable

26 viewsexcelvba
0

I’m using a serch criteria to get the number/index location of a column to use in the field section of the autofilter. Getting an error "runtime err 1004: Autofilter method of range class failed" not sure if it’s possible. I can see in the degug the variable is holding the correct number


Private Sub cmdExtract1_Click()
Dim ws As Worksheet
    Dim lngLastRow As Long
    Dim rngData As Range
    Dim iColNumber As Integer
    
    
     Dim strSearch As String
    Dim aCell As Range
  
    Set ws = Worksheets("Detail Excel")
    ws.Activate


    'Identify the last row and use that info to set up the Range
    With ws
    ws.Range("1:1").Select
        lngLastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

strSearch = "Deleted App"

    Set aCell = Sheet1.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
     iColNumber = aCell.Column
         
    End With
    

'Offer Date: include dates, remove blanks
Application.DisplayAlerts = False 'switching off the alert button
ws.Range("A1" & ":y" & lngLastRow).AutoFilter Field:=iColNumber, Criteria1:=""
ws.Range("A2" & ":y" & lngLastRow).SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True 'switching on the alert button

On Error Resume Next
ws.ShowAllData

search variable then set the cell column number to a variable