Hi there -
I'm familiar with INDIRECT in Excel but struggling with the syntax when trying to use an indirect reference in a VBA range.
Following is a macro which selects simple range (i.e. A7:G26 )
So the range references G26 and D26 need to reflect the last row reference held in cell D2.

Sub Sort_Using_Indirect_Ref()
' Sort using indirect reference - Row number held in cell D2
Range("A7:G26").Select
ActiveWorkbook.Worksheets("Table-Transactions").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Table-Transactions").Sort.SortFields.Add Key:= _
Range("D7:D26"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Table-Transactions").Sort
.SetRange Range("A7:G26")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

cheers
Chris