Yes, I see it now. Thank you, Richard.
I was just coming back to report that I'd found a solution - which is similar in concept to your ideas.
Dim sRng As String
Dim sKRange As String
Dim sSrange As String
With Worksheets("Films")
Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
sRng = Selection.Address 'Get the selected Range spec as a string
End With
sBRow = Mid(sRng, InStr(sRng, ":") + 4) 'Get the bottom row from the string
sKRange = "$E$5:$E$" & sBRow 'Create the sort key range as a string
sSrange = "$A$5:$E$" & sBRow 'Create the data sort range as a string
ActiveWorkbook.Worksheets("Films").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Films").Sort.SortFields.Add Key:=Range(sKRange) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Films").Sort
.SetRange Range(sSrange) 'Apply them to the sort statement
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
- and now I see your followup about the value of dynamic range names. I'll study that too.
Thanks again for your help.
- Fred
Bookmarks