Broken Excel VBA Macro Run-time error '1004

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:

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.

Answer 1

The answer is that the referenced field on the line with the error has been changed. If you update the field name in this formula to the correct one then the code will execute with no problems.

But, in the future it's probably safer to refer to table columns by their index number rather than the field name unless you're 100% sure that it will not change.

Answer 2

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