Dynamic Ranges....

Hi,

I have some vba code that does some sorting on a predefined range like so:

Private Sub cmdStatusDateScoreSort_Click()
    ' Declare range to sort
    Dim strSortRange As String
    ' Declare ranges of columns to sort by
    Dim strStatusRange As String
    Dim strResolveDateRange As String
    Dim strScoreRange As String
    
    ' Initialise range to sort
    strSortRange = "B5:U104"
    ' Initialise ranges of columns to sort by
    strStatusRange = "D5"
    strResolveDateRange = "R6"
    strScoreRange = "K6"
    
    ' Sort range using Excel's range sort function
    Range(strSortRange).Sort _
                Key1:=Range(strStatusRange), Order1:=xlAscending, _
                Key2:=Range(strResolveDateRange), Order2:=xlAscending, _
                Key3:=Range(strScoreRange), Order3:=xlDescending, _
                Header:=xlGuess, OrderCustom:=1, _
                MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal, _
                DataOption2:=xlSortNormal, _
                DataOption3:=xlSortNormal

However, i no longer want the sort range of B5:U104 to be static as i want the user to be able to add new rows and when they click on the sort macro button, the sort picks up the newly added rows.

Can anyone help me with this?

Thanks in advance.

Thanks,

however, it doesnt like that.

Could it be because rngSortRange is first declared as a string but then initialized as something else i.e.

' Initialise range to sort
strSortRange = Range("B5:U65536").End(xlUp)

I don't know, im a beginner so that may be complete nonsense.

Any ideas?

Ammended as advised, but i get a 'run time error '1004' Application-defined or object-defined error'

Private Sub cmdRiskSort_Click()
    ' Declare range to sort
    Dim strSortRange As Range
    ' Declare ranges of columns to sort by
    Dim strScoreRange As String
    Dim strProbRange As String
    Dim strImpactRange As String
    
    ' Initialise range to sort
    Set strSortRange = Range("B5:U65536").End(xlUp)

    ' Initialise ranges of columns to sort by
    strScoreRange = "K5"
    strProbRange = "I5"
    strImpactRange = "J5"
    
    ' Sort range using Excel's range sort function
    Range(strSortRange).Sort _
                Key1:=Range(strScoreRange), Order1:=xlDescending, _
                Key2:=Range(strProbRange), Order2:=xlDescending, _
                Key3:=Range(strImpactRange), Order3:=xlDescending, _
                Header:=xlGuess, OrderCustom:=1, _
                MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal, _
                DataOption2:=xlSortNormal, _
                DataOption3:=xlSortNormal
End Sub

Thanks