asked on

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")

Thanks!
Kelvin

Martin Liss

Set rn = Columns("A:" & Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column)

Roger

ASKER

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?

Kelvin

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roger

ASKER

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.

Regards,
Kelvin
Range-Sort-method-5-Aug-2014---MatinLiss

Roger

ASKER

Thanks... our mssgs overlapped.
Opening yours now!
K

Roger

ASKER

Thanks! Fast and effective, again!
strLastCol gave me what I needed, and it drives the code I attached, just as required.

Cheers,
Kelvin

Martin Liss

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