Results 1 to 4 of 4
Like Tree1Likes
  • 1 Post By mikerickson

How to sort data in columns with VBA instead of Excel's sort function

This is a discussion on How to sort data in columns with VBA instead of Excel's sort function within the Excel Questions forums, part of the Question Forums category; Hi. I'm a novice when it comes to Excel VBA . Need help with this one. I can't figure out ...

  1. #1
    New Member
    Join Date
    Jan 2012
    Posts
    46

    Default How to sort data in columns with VBA instead of Excel's sort function

    Hi. I'm a novice when it comes to Excel VBA. Need help with this one. I can't figure out how to assign the same macro to different cells, instead of having a specific macro for a specific column. *Like for example, if I were to use the following code to sort a selected header in ascending order:

    Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes

    How can I make the Range (Cell A1) and Key (Cell A2) variable rather that static??

    Thanks so much!

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,728

    Default Re: How to sort data in columns with VBA instead of Excel's sort function

    First, there are problems with that code. Sorting a single cell is a bit of a waste. Plus the keys of a sort have to be inside the range sorted.

    Here is an example of how to assign ranges to variables and use them in a sort

    Code:
    Dim oneRange as Range
    Dim aCell as Range
    
    Set oneRange = Range("A1:G10")
    Set aCell = Range("B1")
    
    oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes
    deependra likes this.

  3. #3
    New Member
    Join Date
    Jan 2012
    Posts
    46

    Default Re: How to sort data in columns with VBA instead of Excel's sort function

    Hello, I tried the above macro but for some reason, it still does not adapt to my selection. Here is table I'm trying to sort and the alteration I made

    State Name Last Appraised Value SF Occ. Average Rent.
    AL Property A $9,000,000.00 150,000 98% $24.00
    CA Property H $12,000,000.00 178,000 90% $42.00
    CO Property I $18,000,000.00 220,000 86% $28.00
    MA Property E $4,200,000.00 36,000 94% $40.00
    MI Property D $8,000,000.00 120,000 86% $36.00
    NJ Property B $5,400,000.00 132,000 80% $49.00
    NJ Property G $2,000,000.00 58,000 81% $39.00
    NM Property F $3,000,000.00 40,000 85% $29.00
    NY Property C $6,000,000.00 85,000 82% $56.00
    WA Property J $4,000,000.00 42,000 71% $45.00


    Sub Sor_t()

    Dim oneRange As Range
    Dim aCell As Range
    Set oneRange = Range("A2:F11")
    Set aCell = Range("A2")
    oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes

    End Sub

    Basically, in other words, I am trying to replicate the same function carried out by Excel's Sort by Ascending Order function. Bear in mind, its not just going to be a selected group of cells within the column, but also the entire rows that the respective cells are located in.

    Therefore, if I were to select the header "Name", and run the macro, the sort order would be ascending according to the names of the properties, at the same time the order for the other columns would also adjust accordingly.


    Thanks for your advice.

  4. #4
    New Member
    Join Date
    Jan 2012
    Posts
    46

    Default Re: How to sort data in columns with VBA instead of Excel's sort function

    Thank you. Modified your macro to make the following:

    Sub Sort_1()

    Dim oneRange As Range
    Dim aCell As Range

    Set oneRange = Selection
    Set aCell = ActiveCell

    oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlGuess


    End Sub

    And it works like a charm!!!

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com