Dynamic Range

[zinah]

Hi,

I have a table that it's macro driven. What I need is to sort this table based on criteria and I have recorded below macro to do that, however, the range is dynamic and it depends on number employees that are reporting to each manager.
What I need is how to write a macro that can sort according to the dynamic range?

The dynamic range macro is as below:

Dim rSht As WorksheetSet rSht = Sheets("Role Scorecard")


Dim j As Integer
    j = rSht.[V13].Row
rSht.[U12].Value = "V12:Z" & j

The recorded macro:

Sub Macro1()

    Range("V14:Z14").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "X14:X67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "Y14:Y67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "Z14:Z67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "W14:W67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Role Scorecard").Sort
        .SetRange Range("V13:Z67")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

How can I make all the end range, i.e. in this recorded example "67" dynamic "X14:X67" knowing that all ranges starts from 14?

:=Range( _
        "X14:X67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "Y14:Y67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "Z14:Z67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "W14:W67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

 

[Michael M]

If you first code creates the variable "j" then use "j" in the second code

ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:= _
        Range("X14:X" & j), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "Y14:Y" & j), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "Z14:Z" & j), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "W14:W" & j), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

 

[zinah]

Thank you Michael I tried your way and it worked, I also tried also another way and it also worked and like to share it with you:

Range("V14:Z" & Range("V" & Rows.Count).End(xlUp).Row).Select     Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "X14:X" & Rows.Count), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "Y14:Y" & Rows.Count), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "Z14:Z" & Rows.Count), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Role Scorecard").Sort.SortFields.Add Key:=Range( _
        "W14:W" & Rows.Count), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

 

[Michael M]

Thanks for sharing......Doesn't really matter how you do it .....if it works for you...

 

[zinah]