VBA Sort Macro - Need to Make it Dynamic

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
780
Hello All, the following code allows me to sort the sheet by B2:B20544 and K2:K20544 with a range A1 to N20544.

How would I change this bit of the coding to make it dynamic - so that if when the number of rows changes?

Thanks a lot.


Cells.Select
ActiveWorkbook.Worksheets("Sheet0").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet0").Sort.SortFields.Add Key:=Range( _
"B2:B20544"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Sheet0").Sort.SortFields.Add Key:=Range( _
"K2:K20544"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet0").Sort
.SetRange Range("A1:N20544")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Mike LH

Well-known Member
Joined
Mar 17, 2015
Messages
566
Hi,

Try this


Code:
Sub somesub()
Dim Lastrow As Long, ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet0")
Lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
 ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range( _
 "B2:B" & Lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
 xlSortNormal
 ws.Sort.SortFields.Add Key:=Range( _
 "K2:K" & Lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
 xlSortNormal
 With ws.Sort
 .SetRange Range("A1:N" & Lastrow)
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With
 End Sub
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,242
Office Version
  1. 365
Platform
  1. Windows
Try this but I haven't tested it:

Code:
Dim lastrow As Integer
lastrow = Sheets("Sheet0").Range("A" & Rows.Count).End(xlUp).Row

ActiveWorkbook.Worksheets("Sheet0").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet0").Sort.SortFields.Add Key:=Range("B2:B" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet0").Sort.SortFields.Add Key:=Range("K2:K" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet0").Sort
    .SetRange Range("A1:N" & lastrow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
 
Last edited:

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
Hi there,

Untested but try this:

Code:
Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    
    lngLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With ActiveWorkbook.Worksheets("Sheet0")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("B2:B" & lngLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("K2:K" & lngLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("A1:N" & lngLastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub

End Sub

Regards,

Robert
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
Thanks a lot guys, works!

That's great and you're welcome :)

Just one point - if you use the nifty solution given by steve the fish, change the lastrow variable to long as an integer can 'only' hold a maximum of 32,767 rows so you may get caught out.

Regards,

Robert
 

Watch MrExcel Video

Forum statistics

Threads
1,122,413
Messages
5,595,998
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top