VBA Sort Macro - Need to Make it Dynamic

[jamescooper]

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

 

[Mike LH]

Hi,

Try this

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]

Try this but I haven't tested it:

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

 

[Trebor76]

Hi there,

Untested but try this:

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

 

[jamescooper]

Thanks a lot guys, works!

 

[Trebor76]

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