Sorting where rows are dynamic.

[gene7135]

I'm new to Excel VBA. This seems like it should be simple though.

Here is my code.

Currently I get a 1004 error. "Method of Range of Object _Global Failed" on "Range(Selection).Sort.SortFields.Clear"

Any help is greatly appreciated.

Sub sortBacklog()

    Dim appExcel As Excel.Application
    Dim myWorkbook As Excel.Workbook
    Dim myWorkSheet As Worksheet
    
    
    Set appExcel = CreateObject("Excel.Application")
    Set myWorkbook = appExcel.Workbooks.Open("C:\Backlog.xls")
    Set myWorkSheet = myWorkbook.Sheets(1)
    
    myWorkSheet.Activate
    myWorkSheet.Select
    
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection).Sort.SortFields.Clear
      
       
  Range(Selection).Sort key1:=Range("F2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
    
    
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    appExcel.Quit
    Set myWorkSheet = Nothing
    Set myWorkbook = Nothing
    Set appExcel = Nothing


End Sub

 

[gene7135]

I am making progress.

Instead of using Clearing the sortfields from the Range Command I clear it from the Worksheet using

myWorkSheet.Sort.SortFields.Clear

Now I receive the same error from

Range(Selection).Sort key1:=Range("F2"), Order1:=xlAscending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal