“AutoFilter method of Range class failed” (Run-time error 1004)

[RapidFireGT]

The VBA below was created by recording a macro that applies AutoFilter to Column A, filters Column A for all rows containing the string “C U S T O M E R L I S T”, and then deletes all of those identified rows. This code executes successfully in Excel 2010 and 2013, but is generating the following error in Excel 2007:

“Run-time error ‘1004’: AutoFilter method of Range class failed

The red text denotes the line of code that the debugger is calling out:

' Removes all rows containing the "C U S T O M E R   L I S T" header
    With ActiveSheet
    .AutoFilterMode = False
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
     [COLOR=#ff0000]   .AutoFilter 1, "*C U S T O M E R   L I S T*"[/COLOR]
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
    End With

Any ideas why 2007 doesn't like this AutoFilter code, and what I can change to correct it?

Thanks in advance!
- Rob

 

[Domenic]

I tested it in Excel 2007, and it works fine...

 

[RapidFireGT]

I tested it in Excel 2007, and it works fine...

Hm... so it does. I copied the specific block of code and tested it in a new worksheet and it did in fact work. Perhaps there is something in the preceding code that is causing the error?

Just to provide some context: this macro has been created to parse a large text file that contains various fields of a database table containing customer information. The only way to extract the data from the legacy system was to export it to a .txt file, and now I'm using VBA to parse the data into separate fields in Excel.

Sub ImportCleanseConvertData()
[B][COLOR=#008000]    ' Prompts user to select a text file and then imports the contents of that file into the "RawCustomerData" worksheet[/COLOR][/B]
    Dim varFileName
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "RawCustomerData"
        varFileName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
[B][COLOR=#008000]  
  ' Exit macro if the user clicks "Cancel"[/COLOR][/B]
    If varFileName = False Then
        MsgBox "File import canceled."
        Application.DisplayAlerts = False
        Sheets("RawCustomerData").Select
        ActiveWindow.SelectedSheets.Delete
        Application.DisplayAlerts = True
        Sheets("Begin Here").Select
        Exit Sub
    End If
    
[B][COLOR=#008000]    ' Otherwise, import the customer data file[/COLOR][/B]
    If TypeName(varFileName) = "String" Then
        With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & varFileName _
        , Destination:=Range("A1"))
        .Name = "ImportCustomerFile"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    End If
    
[COLOR=#008000][B]' Deletes the first 3 rows of the file (header information; no customer data)[/B][/COLOR]
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    
[B][COLOR=#008000]' Deletes the last 30 rows of the file (header information; no customer data)[/COLOR][/B]
    Dim rLastRow As Range
    Set rLastRow = Cells(Rows.Count, "A").End(xlUp)
    rLastRow.Offset(-29).Resize(30).EntireRow.Delete
[B][COLOR=#008000]
' Removes all blank rows from the raw customer file[/COLOR][/B]
    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    
[COLOR=#008000][B]' Removes all rows containing the "C U S T O M E R   L I S T" header[/B][/COLOR]
    With ActiveSheet
    .AutoFilterMode = False
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR=#ff0000]        .AutoFilter 1, "*C U S T O M E R   L I S T*"[/COLOR]
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
    End With

 

[Domenic]

The error can occur if Column A is completely empty, no column header, and no values...