Broken Excel VBA Macro Run-time error '1004': Method 'Range' of object '_Global' failed

Question

I have a macro, called "Hide Completed", that has worked since April. The macro is supposed to put the items in order by completion date then hide the rows that have data in the date completed field. For some reason, it failed last week and I haven't been able to determine what went wrong. An identical macro is running on another tab (different names for tables and field names), and it works fine. I haven't found any threads on Stack Overflow (or other site) that are close enough to my problem to be of assistance. Here's the Macro:

    Sub hideCompleted()
'

' showCompleted Macro
'
    Application.ScreenUpdating = False
'
    Range("A1").Select
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1

' sortDataByDate Macro
'
    ActiveWorkbook.Worksheets("Project List").ListObjects("Table2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Project List").ListObjects("Table2").Sort.SortFields.Add _
        Key:=Range("Table2[[#All],[Complete" & Chr(10) & "Date]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Project List").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'

' hideCompleted Macro
'

'
    Range("A2").Select
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:="="

    Application.ScreenUpdating = True

End Sub

Excel is calling attention to: Run-time error '1004': Method 'Range' of object '_Global' failed.

But the highlighted code in the debugger is: highlighted code in debugger

I have checked the number of characters in individual cells to see if I'm over the 911 character limit (especially cells in the comment column - column F). That isn't the case. I'm also attaching an image of the excel worksheet to give an idea how it is used. Any help is appreciated. enter image description here

I haven't found any threads on Stack Overflow (or other site) that are close enough Which threads? There is one very common cause for this error... — David Zemens
Did you perhaps recently changed the label of the first column from "Complete & Chr(10) & Date" to "Complete & Chr(10) & Date/closed"? Your code is referencing the former whilst the image of your table contains the latter. — Excel Developers
David, i entered the text "Run-time error '1004': Method 'Range' of object '_Global' failed" into the question box and reviewed all of the links in the suggested questions to confirm that mine wasn't already covered. — B-Rell
Excel Developers, that was the problem. THANK YOU. It always amazes me how something so simple can be overlooked. I went on a 3+ hour goose-chase trying to find a problem in the code when it was really the spreadsheet (operator error) all along. — B-Rell

David Zemens David Zemens · Accepted Answer · 2014-08-18T19:37:56

If your problem is what I think it is, then many other answers will resolve it.

The parameter you've provided for the Key argument is not fully qualified, so it is implicitly doing:

ActiveWorkbook.Worksheets("Project List").ListObjects("Table2").Sort.SortFields.Add _
    Key:=ActiveSheet.Range("Table2[[#All],[Complete" & Chr(10) & "Date]]"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortTextAsNumbers

Of course this will likely fail if the ActiveSheet is any sheet other than "Project List" at run-time.

Resolve it by qualifying the Range object to the appropriate worksheet:

Dim wsProjectList as Worksheet
Set wsProjectList = ActiveWorkbook.Worksheets("Project List")

wsProjectList.ListObjects("Table2").Sort.SortFields.Add _
    Key:=wsProjectList.Range("Table2[[#All],[Complete" & Chr(10) & "Date]]"), _
    SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortTextAsNumbers

Broken Excel VBA Macro Run-time error '1004': Method 'Range' of object '_Global' failed

2 Answers