Excel VBA Sorting

Question

I'm new to Excel VBA. This seems like it should be simple though. I need to sort a spreadsheet from a Access database.

Here is my code.

Currently I get a 1004 error. "Method of Range of Object _Global Failed" on "myRange = Range(Selection)"

Any help is greatly appreciated.

Sub sortBacklog()

Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook
Dim myWorkSheet As Worksheet
Dim myRange As Range

Set appExcel = CreateObject("Excel.Application")
Set myWorkbook = appExcel.Workbooks.Open("C:\Users\gephilli\Desktop\Dispatch\SAP_Backlog.xls")
Set myWorkSheet = myWorkbook.Sheets(1)

myWorkSheet.Activate
myWorkSheet.Select

Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
myRange = Range(Selection)

myWorkSheet.Sort.SortFields.Clear

ActiveWorkbook.Worksheets("PLS Depot Backlog Report").Sort.SortFields.Add Key _
    :=Range("F2:F20491"), SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("PLS Depot Backlog Report").Sort
    .SetRange Range(myRange)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ActiveWorkbook.Save
ActiveWorkbook.Close
appExcel.Quit
Set myWorkSheet = Nothing
Set myWorkbook = Nothing
Set appExcel = Nothing
End Sub

Answer 1

It is normally unnecessary to select anything in VBA. That is what recorded macros do, but it's not the efficient way. Try: Set myrange = Sheet1.Range("B1", Sheet1.Range("B1").End(xlDown).End(xlToRight))

The problem you are seeing might actually be the lack of a "set" in the line where the error occurs. Without a "set" excel tries to work on range contents, with "set" it works on the range objects themselves.

Answer 2

Taking into account comments and better practice I've re-written your code with comments:

Sub sortBacklog()

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Changed to late binding - no need to set reference to Excel  '
    'and not reliant on a specific version of Excel.              '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim appExcel As Object
    Dim myWorkbook As Object
    Dim myWorkSheet As Object
    Dim myRange As Object

    Set appExcel = CreateObject("Excel.Application")
    'appExcel.Visible = True
    Set myWorkbook = appExcel.Workbooks.Open("C:\Users\gephilli\Desktop\Dispatch\SAP_Backlog.xls")

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'You may want to change this to look at a named sheet.
    'e.g. myWorkbook.Sheets("PLS Depot Backlog Report")    '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Set myWorkSheet = myWorkbook.Sheets(1)

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Everything between 'With' and 'End With' that       '
    'starts with a . (period) will apply to myWorksheet. '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    With myWorkSheet

        '''''''''''''''''''''''''''''''''''''''''''
        'Get range from B1 to last cell on sheet. '
        '''''''''''''''''''''''''''''''''''''''''''
        Set myRange = .Range(.Cells(1, 2), .Cells(.Cells.Find("*", , , , 1, 2).Row, .Cells.Find("*", , , , 2, 2).Column))

        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'Everything between 'With' and 'End With' that            '
        'starts with a . (period) will apply to myWorksheet.Sort. '
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        With .Sort
            .SortFields.Clear

            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            'As using late binding Access won't understand Excel values so: '
            'xlSortOnValues = 0     xlYes           = 1                     '
            'xlAscending    = 1     xlTopToBottom   = 1                     '
            'xlSortNormal   = 0     xlPinYin        = 1                     '
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            .SortFields.Add _
                Key:=myRange.offset(, 6).Resize(, 1), _
                SortOn:=0, _
                Order:=1, _
                DataOption:=0

            .SetRange myRange
            .Header = 1
            .MatchCase = False
            .Orientation = 1
            .SortMethod = 1
            .Apply

        End With

    End With

    With myWorkbook
        .Save
        .Close
    End With

    appExcel.Quit

    Set myWorkSheet = Nothing
    Set myWorkbook = Nothing
    Set appExcel = Nothing

End Sub