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

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

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