Macro To Sort Data Ascending and Decending Using VBA

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! :)

Try this..

Code:

---

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

---

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

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! :)

Quote:

---

Originally Posted by 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?:

Code:

---

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

---

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

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

Code:

---

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

---

Quote:

---

Originally Posted by Excel Fox

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

Code:

---

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! :)