Macro To Sort Data Ascending and Decending Using VBA

[olives]

I've never done macros before, but I thought I'd ask how to do a macro to do the following:


1. Select Columns J:AG
2. Sort first by Column AG in ascending order(Lowest to Highest)
3. Sort then by Clolumn J in descending order (Highest to Lowest)

Could I just click on a button or a cell to run this macro? Thanks!

[Excel Fox]

Try this..

Sub ExcelFox()
    
    Dim lng As Long
    
    With Worksheets("Sheet1")
        lng = Application.Max(.Cells(.Rows.Count, "J").End(xlUp).Row, 2)
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("AG2:AG" & lng), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("J2:J" & lng), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SetRange .Range("J1:AG" & lng)
        .Sort.Header = xlYes
        .Sort.MatchCase = False
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
    End With
    
End Sub

[Excel Fox]

You can insert a shape and assign the macro to this, or call the routine from within the click event of a command button.

[olives]

Ok, since I'm new to this... A few questions...
The 'macro' is called ExcelFox, right?...
It's a module? Right? So Ican hit Alt-F11, paste this code into a Module, right?
Then I can run it immediately or I can call it by going to 'Developer' then 'Macros', then running it, right?
Once I run it, that's it! Right?...

Sorry for all the 'Rights'!

I'll try it when I get home tonight!

[olives]

... I'll try it when I get home tonight!

Just tried it out, Fox! It looks good! Thanks!

Let me ask you this... It doesn't seem to be incluidng the top row, row 1, in the sort, what do I have to change in the code to include the top row?...

Does it have to do with this row?:

lng = Application.Max(.Cells(.Rows.Count, "J").End(xlUp).Row, 2)

Is the 2 at the end what I should change to 1? Thanks!

[Excel Fox]

No that's not what you need to do. Here you go...

Sub ExcelFox()
    
    Dim lng As Long
    
    With Worksheets("Sheet1")
        lng = .Cells(.Rows.Count, "J").End(xlUp).Row
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("AG1:AG" & lng), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("J1:J" & lng), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SetRange .Range("J1:AG" & lng)
        .Sort.Header = xlNo
        .Sort.MatchCase = False
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
    End With
    
End Sub

[olives]

No that's not what you need to do. Here you go...

Sub ExcelFox()
    
    Dim lng As Long
    
    With Worksheets("Sheet1")
        lng = .Cells(.Rows.Count, "J").End(xlUp).Row
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("AG1:AG" & lng), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("J1:J" & lng), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SetRange .Range("J1:AG" & lng)
        .Sort.Header = xlNo
        .Sort.MatchCase = False
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
    End With
    
End Sub

Wow! Amazing! Thanks!