-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
sort col. using formula or macro
- Thread starter dwbrown
- Start date
sweater_vests_rock
Well-known Member
- Joined
- Oct 8, 2004
- Messages
- 1,657
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:
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.
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:
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 Sub5) 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.
Upvote
0
Anne Troy
MrExcel MVP
- Joined
- Feb 18, 2002
- Messages
- 2,632
- Office Version
- 365
- Platform
- Windows
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.
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.
Upvote
0
