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

Posted on 2014-08-06
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
    LVL 44

    Expert Comment

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

    Author Comment

    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 44

    Accepted Solution

    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


    Author Comment

    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

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

    Author Closing Comment

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

    LVL 44

    Expert Comment

    by: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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now