auto sort
Try the code below...remember, right-click on the tab of the worksheet that
you want the code to run in (this is a €˜Private subroutine, so it will only
affect the sheet that you paste the code into, as opposed to affecting all
the sheets in the entire workbook).
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long
If Target.Column < 10 Then Exit Sub
Application.ScreenUpdating = False
EndData = Cells(Rows.Count, 1).End(xlUp).Row
With Range(Cells(2, 1), Cells(EndData, 3))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
Application.ScreenUpdating = False
End Sub
Two lines of code are important he
Line #1:
If Target.Column < 10 Then Exit Sub
This tells Excel to do the sort after you finish with entering data into
ColumnJ (i.e., when you reach ColumnK)
Line #2:
..Sort Key1:=Range("B2"),€¦€¦..etc.
This tells Excel to sort according to the data in ColumnB.
If your data doesnt extend to ColumnJ, or extends past columnJ, change
Line#1. Also, if you want to sort according to a different column, change
this: Range("B2")
Hope that helps.
Write back if you have another question.
Regards,
Ryan---
--
RyGuy
"Vicki" wrote:
Hi Ryan,
So I cut and past the code and then I updated colum J's numbers and it didnt
automatically sort. I'm sorry thanks for you patience
"ryguy7272" wrote:
Right-click on the WS where you want to do your sort and paste this code into
the window that pops 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
Be mindful of the fact that the current sort column is defined as:
.Sort Key1:=Range("B2")
Change to suite your needs.
Regards,
Ryan---
--
RyGuy
"Vicki" wrote:
I currently am working on a excel spreadsheet that needs to be a working
database. It is currently linked to bloomberg, (stock prices are
automatically fed and updated) (live feed) The only column that will change
everyday is the last one, although when the last one changes the positions of
the stocks need to be sorted based on that last column in ascending order.
can anyone assist me with this. I am on a deadline and have been working on
this project for the last week, not which direction i should be going with
this. (vlookup, macro) not sure. anyone's help would be greatly appreciated.
|