sort col. using formula or macro

[dwbrown]

Hello Excel gurus, I have an application where it would be helpful if I could initiate a col. sort using a formula or macro in a cell. As opposed to the menu sort options. Please advise, David.

 

[sweater_vests_rock]

dwbrown.

If you wish to sort the same range every time, the use the following steps:
-------------------------
1) insert a hyperlink into a cell on your worksheet. make this hyperlink refer to the cell itself.

2) open the vbeditor and look at the project menu. double-click on the worksheet containing the hyperlink.

3) paste the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Range("XX").Sort Key1:=Range("YY"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

End Sub

4) Replace "XX" with the address of the range you wish to sort ("A1:A5", "I:I", etc.)

5) Replace "YY" with the address of the range key (the cell at the top of the column you wish to sort by)
---------------------------
To run this code, all you have to do is click on the hyperlink. If you wish to do a descending sort rather than ascending, change Order1:=xlAscending to Order1:=xlDescending.

Hope that helps. Ben.

 

[Anne Troy]

Record a macro that does the sort you want.
See: http://www.theofficeexperts.com/officevba.htm#ExcelVBA

When you're done, open the VBE (Tools-Macro-Visual Basic Editor).
You'll see your code in a module on the left. The code starts with Sub Macroname() and ends with End Sub. You'll want to take what is in between and copy it. Delete the sub/endsub lines.

On the left- double-click on your worksheet to be sorted. Top-right, choose Workbook from the left-hand-side dropdown.

The following should appear:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Between those two lines, paste the code you copied a minute ago. Close the VBE. Change any cell in the worksheet and it should automatically sort.

As always, make a backup of your file before you do all this stuff.