Auto Sort A List by Values in Excel

[Andrew in Sydney]

Hi fom Sydney!

Is there a way in Excel to have a list of names with values automatically re-sort whenever any of the values change.

The default sort I have in mind is ascending by values.

eg.

CITY VALUE
Sydney 100
Boston 200
London 300
Moscow 400
Houston 500

The values change to:

Sydney 5000
Boston 250
London 8000
Moscow 300
Houston 50

I now want Houston 1st, Boston 2nd etc - automatically - without me having to do a sort again by Value.

Any help with this highly apreciated.

Andrew Lawless
Sydney - Australia

 

[Tom Urtis]

Assuming "City" and "Value" are headers for separate columns, maybe A and B on row 1, then try this:

Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Or Target.Cells.count > 1 Then Exit Sub
Dim SortRange As Range
Set SortRange = Range(("A1"), Cells(Rows.count, 2).End(xlUp))
SortRange.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes
End Sub


When you enter a new number in colum B, the range will sort itself.

 

[Ekim]

Hi Andrew,

Using Excel XP

The following macro assumes that your data is in A1:B12, with the headings “CITY” and “VALUE” in cells A1 and B1 respectively (change to suit).

Put this macro in the appropriate worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column = 2 Then
      Range("A2:B12").Sort _
       Key1:=Range("B2"), Order1:=xlAscending, _
         Key2:=Range("A2"), Order2:=xlAscending, _
         Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
         Orientation:=xlTopToBottom
     End If
   
End Sub

The macro will sort values first, then by city i.e. if two cities have the same value, the macro will sort the cities alphabetically.

HTH

Mike

 

[Andrew in Sydney]

Thanks heaps!

If my City column was 'B' and my Value column 'L', how would I modify the VB to account for this?

Andrew

 

[Tom Urtis]

You should specify the entire column range you want sorted. Is column A involved? Column DG? What is the actual column involvement that you want included?

 

[Andrew in Sydney]

Columns A through to L.
The value data to sort by is in L.
The data goes down to row 8 in this example.
There is a merged cell in row 9 that extends all the way to column L (not sure if this presents a problem).
Thanks again
Andrew

 

[Tom Urtis]

I hate merged cells.

See if this does what you want, in place of the first one I sent:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 12 Or Target.Cells.count > 1 Then Exit Sub
Dim SortRange As Range
Set SortRange = Range(("A1"), Cells(Rows.count, 12).End(xlUp))
SortRange.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlYes
End Sub

 

[Andrew in Sydney]

Beautifully Tom!

One more twist....

what if I wanted the range to sort 1st by column L and then by column K, both ascending!

Andrew

 

[Tom Urtis]

What should trigger the sort? An entry in K? An entry in L? An entry anywhere? When the sheet activates? Details please.

 

[Andrew in Sydney]

Any changes in L.

Column L cells contain VLOOKUPs that reference to another sheet in the workbook. Is this an issue? Manual changes are made to the cells in the other worksheet, which then reflect in changes to L.