CODEWITHSUNDEEP
excelsortingvba
chris
chris

Reputation: 565

Excel-VBA Sort doesn't work

I have a problem with a sort function. It doesn't work. If I record a macro while sorting manually, the recorded looks like mine. The Sub looks as follows:

Public Sub sortSelectionByDate(ByRef wrksheet As Worksheet, ByVal fromRow As Integer, ByVal toRow As Integer)
    'debug
    toRow = toRow - 1
    wrksheet.Select
    wrksheet.Rows(fromRow & ":" & toRow).Select
    With Selection
        .Sort Key1:=Range("A9"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
End Sub

Thank you in advance....

Christoph

Upvotes: 0

Views: 21001

Answers (1)

Banjoe
Banjoe

Reputation: 1768

It's most likely not working because you're passing a worksheet that isn't the active worksheet, not activating it, then trying to sort. Here's a link to a description of the problem you're having: Beginning VBA: Select and Activate. Long story short, never ever use .Select or .Selection unless you want your user to be able to run code on the selection of his choice. Even then, still not always a great idea. Here's your sub re-written in one line:

Public Sub sortSelectionByDate(ByRef wrksheet As Worksheet, ByVal fromRow As Integer, ByVal toRow As Integer)
    wrksheet.Rows(fromRow & ":" & toRow - 1).Sort Key1:=wrkSheet.Range("A9"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

The important changes are:

  • No need to use .Select. If you did want to use it you'd need to use wrkSheet.Activate first.
  • Change Key1:=Range("A9") to Key1:=wrkSheet.Range("A9"). Always helps to be explicit when working with cells/ranges.
  • The With statement isn't needed since you're only running one method.

Upvotes: 4

Related Questions