Sorting A Worksheet using VBA

[Hobolord]

Hello,

I am using Excel 2013 on Windows 7 on a PC.

I am attempting to sort a worksheet on several columns in my code. The rest of the code is dependent on this sort being executed sucessfully.

I derived the below using the recorder, but even after a couple of modifications, the sort does not work the same through the code as when I perform it manually, and I am at a loss as to why.


Can anyone see any glaring mistakes that would cause the sort to not be performed correctly? Note: The data begins on row 2 with the headers on Row 1.

 Dim lastrow       As Long

Rows("1:1").Select
ActiveWorkbook.Worksheets("SalesRep").AutoFilter.Sort.SortFields.Clear


lastrow = Cells(Rows.Count, 1).End(xlUp).Row


Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("SalesRep").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SalesRep").AutoFilter.Sort.SortFields.Add Key:= _
        Range("Z2:Z" & lastrow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("SalesRep").AutoFilter.Sort.SortFields.Add Key:= _
        Range("W2:W" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("SalesRep").AutoFilter.Sort.SortFields.Add Key:= _
        Range("A2:A" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("SalesRep").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Thanks for your time!

Hobo

 

[Joe4]

Take a look at this thread here and see if it helps: http://www.mrexcel.com/forum/excel-...e-visual-basic-applications-sorting-code.html

 

[Hobolord]

Hello Joe,

Thanks for the reply.

How would I use multiple criteria for this method:


ActiveSheet.Sort.SortFields.Clear
Columns("A:AC").Sort Key1:=Range("Z2"), Order1:=xlDescending, Header:=xlYes


Thank you,

Hobo

 

[Hobolord]

Got it. Thanks for the reference point.

ActiveSheet.Sort.SortFields.Clear
    Columns("A:AC").Sort Key1:=Range("Z2"), Order1:=xlAscending, Key2:=Range("W2"), Order1:=xlAscending, Key3:=Range("A2"), Order1:=xlAscending, Header:=xlYes