Thread: AutoFilter method of Range class failed

Hi All,

I have this problem which I have been unable to find a suitable solution for online.

I have a UserForm with tickboxes which are linked to cells in ws "Dashboard". These cells (TRUE/FALSE) then feed to the ws "CHART DATA" and toggle whether or not certain rows should be displayed.

The following code is designed to 1) clear all existing filters; 2) sort the data in 4 different sections; 3) reapply the filter to hide those rows that do not meet the relevant criteria (="<>FALSE").

Sub VT_Filter()

Dim ws As Worksheet
Set ws = Sheets("CHART DATA")
    With ws.Range("$A$8:$T$305")
        .AutoFilter 
        .AutoFilter Field:=7
        .AutoFilter Field:=2
    End With 'CLEARS THE FILTERS
    
    ws.Sort.SortFields.Clear
     ws.Sort.SortFields.Add Key:=Range( _
         "B9:B50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal 'SORT BY PCT FOR FIRST CHART
     ws.Sort.SortFields.Add Key:=Range( _
         "D9:D50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal 'THEN SORT BY QUALIFIER FOR FIRST CHART
     With ws.Sort
         .SetRange Range("A9:T50")
         .Header = xlGuess
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With
    
    ws.Sort.SortFields.Clear
     ws.Sort.SortFields.Add Key:=Range( _
         "B52:B121"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal 'SORT BY PCT FOR SECOND CHART
     ws.Sort.SortFields.Add Key:=Range( _
         "D52:D121"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal 'THEN SORT BY QUALIFIER FOR SECOND CHART
     With ws.Sort
         .SetRange Range("A52:T121")
         .Header = xlGuess
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With
  
    ws.Sort.SortFields.Clear
     ws.Sort.SortFields.Add Key:=Range( _
          "B123:B206"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal 'SORT BY PCT FOR THIRD CHART
     ws.Sort.SortFields.Add Key:=Range( _
         "D123:D206"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal 'THEN SORT BY QUALIFIER FOR THIRD CHART
     With ws.Sort
         .SetRange Range("A123:T206")
         .Header = xlGuess
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With

    ws.Sort.SortFields.Clear
     ws.Sort.SortFields.Add Key:=Range( _
         "B208:B305"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal 'SORT BY PCT FOR FOURTH CHART
     ws.Sort.SortFields.Add Key:=Range( _
         "D208:D305"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortNormal 'THEN SORT BY QUALIFIER FOR FOURTH CHART
     With ws.Sort
         .SetRange Range("A208:T305")
         .Header = xlGuess
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With
    
    With ws.Range("$A$8:$T$305")
        .AutoFilter Field:=2, Criteria1:="<>False"
        .AutoFilter Field:=7, Criteria1:="<>False"
    End With 'FILTERS OUT CELLS CONTAINING 'FALSE'
End Sub

When I run it as a macro, there are no problems, but when it runs as part of the routine from the UserForm (i.e. each time a tick box is un/checked) I get the "Run-time error '1004': AutoFilter method of Range class failed" error message. The debugger reports the line highlighted above.

If I comment out that first section that clears the filters, I just get the same error when the code gets to the final section.

I can confirm that I'm using Excel 2007 and the worksheet is not protected. Any ideas? :-/

Thanks so much for any help, I have wasted a lot of time trying to solve this already!
Cheers,
AdLoki

You didn't protect the sheet somewhere ? oops rereading again I missed your comment on that. (Although I suspect that in the userform_initialize event there's some protecting...)
I tried to reduce your code a little....

Sub VT_Filter()
  With Sheets("CHART DATA")
    .Range("$A$8:$T$305").AutoFilter 
    
     for j=1 to 4 
       .cells(choose(j,9,52,123,208),1).currentregion .Sort .cells(choose(j,9,52,123,208),2),,.cells(choose(j,9,52,123,208),4)
     next    
    
    With .Range("$A$8:$T$305")
        .AutoFilter 2, "<>False"
        .AutoFilter 7, "<>False"
    End With
  end with
End Sub

@snb
You seem to have an unwanted space between 'currentregion' and '.Sort'