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

[Riptake]

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!

 

[mikerickson]

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

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

 

[Riptake]

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

<table border="0" cellpadding="0" cellspacing="0" width="520"><colgroup><col style="mso-width-source:userset;mso-width-alt:1426;width:29pt" width="39"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:5046;width:104pt" width="138"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:29pt" height="20" width="39">State</td> <td class="xl63" style="width:66pt" width="88">Name</td> <td class="xl63" style="width:104pt" width="138">Last Appraised Value</td> <td class="xl63" style="width:72pt" width="96">SF</td> <td class="xl63" style="width:48pt" width="64">Occ.</td> <td class="xl63" style="width:71pt" width="95">Average Rent.</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">AL</td> <td class="xl67">Property A</td> <td class="xl64">$9,000,000.00 </td> <td class="xl65"> 150,000 </td> <td class="xl66">98%</td> <td class="xl64">$24.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">CA</td> <td class="xl67">Property H</td> <td class="xl64">$12,000,000.00 </td> <td class="xl65"> 178,000 </td> <td class="xl66">90%</td> <td class="xl64">$42.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">CO</td> <td class="xl67">Property I</td> <td class="xl64">$18,000,000.00 </td> <td class="xl65"> 220,000 </td> <td class="xl66">86%</td> <td class="xl64">$28.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">MA</td> <td class="xl67">Property E</td> <td class="xl64">$4,200,000.00 </td> <td class="xl65"> 36,000 </td> <td class="xl66">94%</td> <td class="xl64">$40.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">MI</td> <td class="xl67">Property D</td> <td class="xl64">$8,000,000.00 </td> <td class="xl65"> 120,000 </td> <td class="xl66">86%</td> <td class="xl64">$36.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">NJ</td> <td class="xl67">Property B</td> <td class="xl64">$5,400,000.00 </td> <td class="xl65"> 132,000 </td> <td class="xl66">80%</td> <td class="xl64">$49.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">NJ</td> <td class="xl67">Property G</td> <td class="xl64">$2,000,000.00 </td> <td class="xl65"> 58,000 </td> <td class="xl66">81%</td> <td class="xl64">$39.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">NM</td> <td class="xl67">Property F</td> <td class="xl64">$3,000,000.00 </td> <td class="xl65"> 40,000 </td> <td class="xl66">85%</td> <td class="xl64">$29.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">NY</td> <td class="xl67">Property C</td> <td class="xl64">$6,000,000.00 </td> <td class="xl65"> 85,000 </td> <td class="xl66">82%</td> <td class="xl64">$56.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">WA</td> <td class="xl67">Property J</td> <td class="xl64">$4,000,000.00 </td> <td class="xl65"> 42,000 </td> <td class="xl66">71%</td> <td class="xl64">$45.00 </td> </tr> </tbody></table>

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.

 

[Riptake]

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!!!

 

[trishcollins]

I have a similar issue. I am sorting three tables, after I load the info on each table into an array. So the table and the header I want to sort on changes with each full loop.

Define the variables and the array

    Dim MyArray As Variant
    Dim MyArrayTable As String
    Dim MyArrayPath As String
    Dim MyColumnName As String

Assign the variables and arrays

     Set myTable = ActiveSheet.ListObjects("EA_Libraries_Data")
     MyArray = myTable.DataBodyRange
     MyArrayTable = MyArray(x, 1)
     MyArrayPath = MyArray(x, 2)
     MyColumnName = MyArray(x, 3)

I perform a bunch of stuff here to populate each table, sorting in between each table population. The population of the table works fine, but it's the sorting where it fails. The following code is where I get an error, specifically on the "Key:=MyColumnName".

        ActiveWorkbook.Worksheets("Starting Directory List").ListObjects(MyArrayTable). _
        Sort.SortFields.Add [B][U][I]Key:=MyColumnName[/I][/U][/B], _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Starting Directory List").ListObjects( _
            MyArrayTable).Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

Any suggestions?