Application of Range.Sort method (vba) dynamically - to a variable number of excel columns.
Posted on 2014-08-06
I need to sort a range in an excel sheet, by automation. I've chosen range.Sort method, but I'm a novice at this method (and inexperienced on Ranges, too). I have a pilot working on a model sheet, but I need to adapt it to deal with dynamic column numbers, because the number of columns to be sorted varies over the life cycle of the excel sheet.
Statically, this uses A1 as key to sort Columns("A:K") for me:
Dim rn As Range
Set rn = Columns("A:K")
rn.Sort key1:=Range("A1"), order1:=xlAscending, header:=xlYes
My question is, how do I express the horizontal range dynamically WITHIN the range.Sort method, in place of the rn specification of rn = Columns("A:K") ??
So far when analysing the sheet dynamically, I find these define horizonal and vertical ranges useful:
Set rn_H = ws_Deploy.Range("A1").End(xlToRight)
plus, Set rn_V = ws_Deploy.Range("A1").End(xlDown)
But I'm uncertain how to modify: Set rn = Columns("A:K")