Sorting code brings up 1004 Application-defined or Object Defined Error

[Frankietheflyer]

The following bit of code brings up the above 1004 error when it gets to .Apply, but for the life of me I can't work out why!

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub DataSort()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim wsRes As Worksheet, wsPoi As Worksheet[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Set wsRes = ThisWorkbook.Sheets("Results")
Set wsPoi = ThisWorkbook.Sheets("Title Points")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Application.ScreenUpdating = False
    
                                'Sort Results
 
 With wsRes.Sort
    .SortFields.Add Key:=Range("AN:AN"), Order:=xlAscending
    .SortFields.Add Key:=Range("AM:AM"), Order:=xlAscending
    .SetRange Range("AL1:AO20")
    .Header = xlYes
    .Apply
  End With
  
                                 'Sort Number of wins against each Team
      
   With wsRes.Sort
    .SortFields.Add Key:=Range("AA:AA"), Order:=xlDescending
    .SetRange Range("AA1:AD6000")
    .Header = xlYes
    .Apply
   End With
   
                                 'Sort Title Points
   
   With wsPoi.Sort
    .SortFields.Add Key:=Range("B:B"), Order:=xlDescending
    .SetRange Range("A1:C600")
    .Header = xlYes
    .Apply
   End With
   
Application.ScreenUpdating = True[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]End Sub

[/FONT]

(The AA1:AD6000 bit are columns of variable length but with several blank rows in so unless I give it specific "length" it only sorts to the first blank)

Can anyone give me an idea of why it throws up the 1004 please?

Many thanks

Frankie

 

[RoryA]

You need to clear the sortfields in between.

 

[Frankietheflyer]

Thanks Rory, but what do you mean by "Clear the sort fields"? If I clear contents there will be nothing to sort!

 

[RoryA]

I mean this:

Sub DataSort()
Dim wsRes As Worksheet, wsPoi As Worksheet
Set wsRes = ThisWorkbook.Sheets("Results")
Set wsPoi = ThisWorkbook.Sheets("Title Points")
Application.ScreenUpdating = False
    
                                'Sort Results
 
 With wsRes.Sort
    .Sortfields.Clear
    .SortFields.Add Key:=Range("AN:AN"), Order:=xlAscending
    .SortFields.Add Key:=Range("AM:AM"), Order:=xlAscending
    .SetRange Range("AL1:AO20")
    .Header = xlYes
    .Apply
  End With
  
                                 'Sort Number of wins against each Team
      
   With wsRes.Sort
    .Sortfields.Clear
    .SortFields.Add Key:=Range("AA:AA"), Order:=xlDescending
    .SetRange Range("AA1:AD6000")
    .Header = xlYes
    .Apply
   End With
   
                                 'Sort Title Points
   
   With wsPoi.Sort
    .Sortfields.Clear
    .SortFields.Add Key:=Range("B:B"), Order:=xlDescending
    .SetRange Range("A1:C600")
    .Header = xlYes
    .Apply
   End With
   
Application.ScreenUpdating = True
End Sub

 

[Frankietheflyer]

Excellent!! I (almost) understand now!! Thanks Rory