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

RapidFireGT

New Member
Joined
Mar 25, 2008
Messages
26
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:
Code:
' 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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I tested it in Excel 2007, and it works fine...
 
Upvote 0
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.

Code:
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
 
Upvote 0
The error can occur if Column A is completely empty, no column header, and no values...
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top