Sort a range dynamically

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
950
Hello All,

Below I have code that was created when I used the macro recorder. The only problem is that I want to apply that code to sort a different range each time I run it.
How can the code below (in red and bold) be modified to sort by the range that contains the bottom-most row of data in column A?



Code:
Sub aaa_test_macro_01()
 
    Cells.Select
    ActiveWorkbook.Worksheets("Totals").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Totals").Sort.SortFields.Add Key:= _
        Range("[B][SIZE=4][COLOR=red]A2:A460[/COLOR][/SIZE][/B]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Totals").Sort.SortFields.Add Key:= _
        Range("[SIZE=4][COLOR=red][B]B2:B460[/B][/COLOR][/SIZE]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Totals").Sort
        .SetRange Range("[SIZE=4][COLOR=red][B]A1:C460[/B][/COLOR][/SIZE]")
        .header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
 
End Sub


Thanks
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
Sub aaa_test_macro_01()
    Dim LR As Long
    With ActiveWorkbook.Worksheets("Totals")
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:= _
            Range("A2:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        .Sort.SortFields.Add Key:= _
            Range("B2:B" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                xlSortNormal
        With .Sort
            .SetRange Range("A1:C" & LR)
            .header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub
 

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
950
Andrew,

Believe it or not it took me this long to apply (my newbie fault :( ) to apply and implement that code. But now I understand it! :)

I LOVE dynamic code that has the ability to change everytime!

Worked perfectly!

Thank you much :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,433
Messages
5,528,738
Members
409,831
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top