autofilter method of range class failed - filter rows based on a flag set in the second column

[fp_excel]

I want to set the filter to second column instead of the first column. If I change .AutoFilter Field:=2 (line 47), I get "autofilter method of range class failed" error. Need help to resolve this issue

Dim wsInput As Worksheet


Sub SplitData_Click()
    Dim wsOutputA As Worksheet
    Dim wsOutputB As Worksheet
    Dim wsOutputC As Worksheet


    Set wsInput = ThisWorkbook.Sheets("Sheet1")
    Set wsOutputA = ThisWorkbook.Sheets("Sheet2")
    Set wsOutputB = ThisWorkbook.Sheets("Sheet3")
    Set wsOutputC = ThisWorkbook.Sheets("Sheet4")


    Dim lrow As Long
    Dim rng As Range


    With wsInput
        .AutoFilterMode = False


        lrow = .Range("A" & .Rows.Count).End(xlUp).Row


        Set rng = .Range("A1:A" & lrow)


        '~~> Filter on AAA
        HandleIt "AAA", rng, wsOutputA


        '~~> Filter on BBB
        HandleIt "BBB", rng, wsOutputB


        '~~> Filter on CCC
        HandleIt "CCC", rng, wsOutputC


        '~~> Filter on blanks
        With rng
            .AutoFilter Field:=1, Criteria1:="="
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With


        .AutoFilterMode = False
    End With
End Sub


Private Sub HandleIt(AFCrit As String, r As Range, wks As Worksheet)
    Dim OutputRow As Long
    Dim filteredRange As Range


    With r
        .AutoFilter Field:=2, Criteria1:=AFCrit
        Set filteredRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
    End With


    If Not filteredRange Is Nothing Then
        With wks
            OutputRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            filteredRange.Copy .Rows(OutputRow)
            filteredRange.ClearContents
        End With
    End If


    wsInput.ShowAllData
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
You have set the Rng as 1 column, therefore you cannot filter on column 2 as there isn't one.