Excel - automatic sorting

Asked By bkune on 19-Feb-08 03:25 PM
I have many columns of corresponding data, i was wondering if there was a way
to not only sort one column but have the coresponding data in other columns
sort with it.  I need an automatic sort beacause the data is to be refreshed
frequently and the actual sort a list comand is not reasonable. If anyone
could help it would be much apreciated, i am co-op and could use all the help
i can get.

Thank you
--




ryguy727 replied on 19-Feb-08 03:43 PM
Try this 'event code' (must right-click the tab of the SS that you are
running it on, select 'view code' and paste it into the window that opens up).
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column <> 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub
Notice!!  You will sort on Column B; change this to suit your needs.


Regards,
Ryan---


--
RyGuy
bkune replied on 19-Feb-08 04:04 PM
i am not real familiar with excel and this seams a little to complex. if it
can me simplified or done in an easier way i would appreciate it.
--
Gord Dibben replied on 19-Feb-08 04:21 PM
ryguy/bkunes

OP wants to sort all columns together by one column.

The code of Sandy Mann's that you posted will only sort column B but not
automatically include the other columns in the sort.

A change to the code..................

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column <> 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With ActiveSheet.UsedRange  '<-----------change made
.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub


Gord Dibben  MS Excel MVP



On Tue, 19 Feb 2008 12:43:06 -0800, ryguy7272
Gord Dibben replied on 19-Feb-08 07:59 PM
You said you want the sort done automatically.

There is no other way to make it "automatic" than by using code.

The code is sheet event code.

Right-click on the sheet tab and "View Code".

Copy/paste the amended code into that sheet module.

Alt + q to return to Excel

Enter something into column B and the Column B will sort in ascending order.
The other columns will stay attached to column B.

If you want a different column to sort on, you must edit the code to reflect
that.

If you want to try this code and need editing, post back with some particulars.


Gord

On Tue, 19 Feb 2008 13:04:01 -0800, bkunes <bkunes@>