We help IT Professionals succeed at work.

Excel: Macro to Sort a Variable Range

dabug80
dabug80 asked
on
Hello,

I have the following macro, which sorts a defined range:

Columns("A:M").Select
    ActiveWorkbook.Worksheets("Sent Emails").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sent Emails").Sort.SortFields.Add Key:=Range( _
        "I2:I593"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Sent Emails").Sort.SortFields.Add Key:=Range( _
        "M2:M593"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sent Emails").Sort
        .SetRange Range("A1:M593")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("I1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColor = 12632256
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0

Open in new window


Note that row references in the above code are for 2:593. Is it possible to instead code this to allow an infinite row range? E.g I2:I23000. I tried I:I and I2:I, but didn't get that to work - so I think I'm missing something.

The row range will always begin at 2.

Cheers.
Comment
Watch Question

Top Expert 2016
Commented:
Hi,

pls try

Columns("A:M").Select
lastRow = ActiveWorkbook.Worksheets("Sent Emails").Range("I" & Cells.Rows.Count).End(xlUp).Row
    ActiveWorkbook.Worksheets("Sent Emails").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sent Emails").Sort.SortFields.Add Key:=Range( _
        "I2:I" & lastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Sent Emails").Sort.SortFields.Add Key:=Range( _
        "M2:M" & lastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sent Emails").Sort
        .SetRange Range("A1:M" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("I1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColor = 12632256
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0

Open in new window

Regards

Author

Commented:
Great. Thanks for your help.