automatic sorting

[bkunes]

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

 

[ryguy7272]

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

 

[bkunes]

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]

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

 

[Gord Dibben]

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