[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2014-08-06
Medium Priority
Last Modified: 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")
Question by:Kelvin4
  • 4
  • 3
LVL 50

Expert Comment

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

Author Comment

ID: 40244045
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?

LVL 50

Accepted Solution

Martin Liss earned 2000 total points
ID: 40244128
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 40244183
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.


Author Comment

ID: 40244186
Thanks... our mssgs overlapped.
Opening yours now!

Author Closing Comment

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

LVL 50

Expert Comment

by:Martin Liss
ID: 40244295
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question