sort col. using formula or macro

dwbrown

New Member
Joined
Nov 21, 2004
Messages
2
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.

:biggrin:
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,603
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top