Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Data auto on cell range when updated
I have viewed threadid=499263 and with that added this modified code to my
main xl worksheet. Private Sub Worksheet_Change(ByVal Target As Range) 'This tells the sub not to work if the column is number 6 or more or 4 or less If ActiveCell.Column = 6 Then End If If ActiveCell.Column <= 4 Then End If Exit Sub 'This tells the sub to only work if Column 5 is modified If ActiveCell.Column = 5 Then 'This is your column range and the E167 below is telling the sort where to start ' I think this range selection area may be my main problem Range(Selection, Selection.End(xlDown)).Select Sheets("KPC Sales Report").Select Range("E167").Select Selection.Sort Key1:=Range("E167"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal End If End Sub This does not do anything when I type data into cell area E167 (column 5) or lower, is it because I am telling it to sort if a column is edited but then trying to tell it to sort only a portion of the column rather than the whole column? I do not write vb code so this is just what I have been able to piece together. I am attempting to sort cells e167 thru f(x) where the (x) will be an end down selection, as this range will grow as new data is entered. I want it to auto sort when a new entry is made in that cell area so the list box that uses this range will always be in alpha order. If there is an easier way to do this please let me know, I may be going in the wrong direction completely. Thanks in advance for any light you can shine on this for me. (the more explanation you can include the better, dont feel like you will be talking down to me I am just trying to learn as I go) Thanks again |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Data auto on cell range when updated
On 18 Jan., 22:44, anduare2
wrote: I have viewed *threadid=499263 and with that added this modified code to my main xl worksheet. Private Sub Worksheet_Change(ByVal Target As Range) 'This tells the sub not to work if the column is number 6 or more or 4 or less * * If ActiveCell.Column = 6 Then * * * * End If * * If ActiveCell.Column <= 4 Then * * * * End If * * Exit Sub 'This tells the sub to only work if Column 5 is modified * * If ActiveCell.Column = 5 Then 'This is your column range and the E167 below is telling the sort where to start ' I think this range selection area may be my main problem * * * * * * * * * * * * Range(Selection, Selection.End(xlDown)).Select * * * * * *Sheets("KPC Sales Report").Select * * * * * * Range("E167").Select * * * * * * Selection.Sort Key1:=Range("E167"), * * * * * * Order1:=xlAscending, * * * * * * Header:=xlGuess, * * * * * * OrderCustom:=1, * * * * * *MatchCase:=False, * * * * * *Orientation:=xlTopToBottom, * * * * * *DataOption1:=xlSortNormal * * End If End Sub This does not do anything when I type data into cell area E167 (column 5) or lower, is it because I am telling it to sort if a column is edited but then trying to tell it to sort only a portion of the column rather than the whole column? I do not write vb code so this is just what I have been able to piece together. *I am attempting to sort *cells e167 thru f(x) where the (x) will be an end down selection, as this range will grow as new data is entered. *I want it to auto sort when a new entry is made in that cell area so the list box that uses this range will always be in alpha order. *If there is an easier way to do this please let me know, I may be going in the wrong direction completely. Thanks *in advance for any light you can shine on this for me. (the more explanation you can include the better, dont feel like you will be talking down to me I am just trying to learn as I go) Thanks again Hi This will sort cells E167 and down cell E167 or lower is changed. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 5 And Target.Row = 167 Then Range("E167", Range("E167").End(xlDown)).Select Selection.Sort Key1:=Range("E167"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End If End Sub Regards, Per |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Data auto on cell range when updated
Fabulous, absolutely incredible. So simple even I got it to work on the
first try. Thank you so much. Everyone that helps on these forums are to be thanked. Mart "Per Jessen" wrote: On 18 Jan., 22:44, anduare2 wrote: I have viewed threadid=499263 and with that added this modified code to my main xl worksheet. Private Sub Worksheet_Change(ByVal Target As Range) 'This tells the sub not to work if the column is number 6 or more or 4 or less If ActiveCell.Column = 6 Then End If If ActiveCell.Column <= 4 Then End If Exit Sub 'This tells the sub to only work if Column 5 is modified If ActiveCell.Column = 5 Then 'This is your column range and the E167 below is telling the sort where to start ' I think this range selection area may be my main problem Range(Selection, Selection.End(xlDown)).Select Sheets("KPC Sales Report").Select Range("E167").Select Selection.Sort Key1:=Range("E167"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal End If End Sub This does not do anything when I type data into cell area E167 (column 5) or lower, is it because I am telling it to sort if a column is edited but then trying to tell it to sort only a portion of the column rather than the whole column? I do not write vb code so this is just what I have been able to piece together. I am attempting to sort cells e167 thru f(x) where the (x) will be an end down selection, as this range will grow as new data is entered. I want it to auto sort when a new entry is made in that cell area so the list box that uses this range will always be in alpha order. If there is an easier way to do this please let me know, I may be going in the wrong direction completely. Thanks in advance for any light you can shine on this for me. (the more explanation you can include the better, dont feel like you will be talking down to me I am just trying to learn as I go) Thanks again Hi This will sort cells E167 and down cell E167 or lower is changed. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 5 And Target.Row = 167 Then Range("E167", Range("E167").End(xlDown)).Select Selection.Sort Key1:=Range("E167"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End If End Sub Regards, Per |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-lock a cell once it's updated | Excel Worksheet Functions | |||
Excel cells randomly don't get updated unless each cell is updated | Excel Discussion (Misc queries) | |||
HOW DO I RETRIEVE DATA FROM THE LAST UPDATED CELL IN A ROW | Excel Worksheet Functions | |||
Dynamic column chart - auto sort on data range | Charts and Charting in Excel | |||
Select updated data from a range of columns | Excel Worksheet Functions |