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