Multiple Sorting with VBA

Question

I have been working on this code for multiple sorting in which I have data from column A to J, I need to sort it till the last row of data. My code consists range values but I need it sort till last row irrespective of whatever range. below is code. Any help is welcome.

Sub sort_s()

Range("A19").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E20:E35" _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F20:F45" _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G20:G35" _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H20:H35" _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("I20:I35" _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A19:J35")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Range("A20").Select
End Sub

izzymo · Accepted Answer · 2015-03-18 07:01:13Z

1

I have slightly modified your code to include references to the last row

Sub sort_s()

Range("A19").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E20:E" & Range("E" & Rows.count).end(xlUp).row _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F20:F"  & Range("F" & Rows.count).end(xlUp).row _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G20:G"  & Range("G" & Rows.count).end(xlUp).row _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H20:H" &  & Range("H" & Rows.count).end(xlUp).row _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("I20:I"  & Range("I" & Rows.count).end(xlUp).row _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A19:J"  & Range("A" & Rows.count).end(xlUp).row)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Range("A20").Select
End Sub

The relevant line i have added is & Range(ColumnName & Rows.count).end(xlUp).Row

answered Mar 18, 2015 at 7:01

izzymo

9361 gold badge10 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

3689 Over a year ago

Thanks Izzymo, That helps. would it be possible for sorting the table as per contains in it like I need to sort row which contains T on top and rest on below it. is there any scope of sorting like it ?

Collectives™ on Stack Overflow

Multiple Sorting with VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related