Application of Range.Sort method (vba) dynamically - to a variable number of excel columns.

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")
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Set rn = Columns("A:" &  Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column)
Kelvin4Author Commented:
Thanks, Martin,
I have the determination, but sadly not the experience to understand  your code.

Is it a substitute for my line: Set rn = Columns("A:K")? or..
Is it a replacement for both line:
Set rn = Columns("A:K")
rn.Sort key1:=Range("A1"), order1:=xlAscending, header:=xlYes

Can you give me some clues?

Martin LissOlder than dirtCommented:
Sub SortVariableColumns()
Dim strLastCol As String
Dim lngLastCol As Long

With ActiveSheet
    lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
    strLastCol = Split(.Cells(1, lngLastCol).Address, "$")(1)
    .Columns("A:" & strLastCol).Select
    Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ', Header:=xlYes
End With
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Columns("A:" & strLastCol)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
End With


End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Kelvin4Author Commented:
Hi Martin:
... I dont think I made it very clear, I want to reorder the rows in a sheet, according to the integer value in one particular 'index' column. If this column were "C" then rows would be sorted on values in C.

I attach an very brief working  example of my code in which data in five columns is sorted either using data in Column A as index, or alternatively data in column B.  Just run subA or subB. These subs contain comment about where I want to dynamically refer to the range of columns over which the data should be sorted. Actually, what I need is for ALL the data on each row to be sorted, no matter how many columns contain that data.

Finally, the data set is not huge, so extreme processing efficiency is not an issue.

Kelvin4Author Commented:
Thanks... our mssgs overlapped.
Opening yours now!
Kelvin4Author Commented:
Thanks! Fast and effective, again!
strLastCol gave me what I needed, and it drives the code I attached, just as required.

Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.