Excel-VBA Sort doesn't work

Question

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

Also write custom fromRow and toRow and in sorting give its header like wrksheet.Rows("A1:D60").Select With Selection .Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With — Emaad Ali
– Emaad Ali, Commented Sep 15, 2011 at 14:17
Hi Emaad, I know it's confusing; But the Selection is correct. Starting Row is always 9. An the Sorting Column is always A. — chris
– chris, Commented Sep 15, 2011 at 14:41
Just write it in customize way, also allow your excel file to be editable. — Emaad Ali
– Emaad Ali, Commented Sep 15, 2011 at 14:43

Banjoe · Accepted Answer · 2011-09-16 04:13:41Z

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.

Collectives™ on Stack Overflow

Excel-VBA Sort doesn't work

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related