Hi all

I have a load of lists in a worksheet that are used for dependent data
validation elsewhere in the workbook.

No problem with any of that, works fine.

However, I need each list to sort itself if a new entry is made in it.
I have a worksheet change macro that works fine, but the only way I
can see how to get it to work for each list individually is by
repeating the code for however many lists there are. For example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Range("A2:A65536").Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("A2"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End
If-----------------------------------------------------------------------------------------------
List 1
If Target.Column = 2 Then
Range("b2:b65536").Sort _
Key1:=Range("b2"), Order1:=xlAscending, _
Key2:=Range("b2"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End
If-----------------------------------------------------------------------------------------------
List 2

and so on for however many lists there are (and that could be 30 or
so)

What I think I need is a means of capturing the column no of the new
list entry and use it as a variable for the "If Target Column="
statement.

Problem is, I can't figure out how to do that!

Can anyone help?

Thanks in advance,

Phil

This might work:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ListCol As String
ListCol = Target.Address
ListCol = Split(ListCol, "$")(1)
MyRange = ListCol & "2:" & ListCol & "65536"
MyKey = ListCol & "2"

Range(MyRange).Sort _
Key1:=Range(MyKey), Order1:=xlAscending, _
Key2:=Range(MyKey), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End


"Philipgrae" wrote:

Hi all

I have a load of lists in a worksheet that are used for dependent data
validation elsewhere in the workbook.

No problem with any of that, works fine.

However, I need each list to sort itself if a new entry is made in it.
I have a worksheet change macro that works fine, but the only way I
can see how to get it to work for each list individually is by
repeating the code for however many lists there are. For example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Range("A2:A65536").Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("A2"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End
If-----------------------------------------------------------------------------------------------
List 1
If Target.Column = 2 Then
Range("b2:b65536").Sort _
Key1:=Range("b2"), Order1:=xlAscending, _
Key2:=Range("b2"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End
If-----------------------------------------------------------------------------------------------
List 2

and so on for however many lists there are (and that could be 30 or
so)

What I think I need is a means of capturing the column no of the new
list entry and use it as a variable for the "If Target Column="
statement.

Problem is, I can't figure out how to do that!

Can anyone help?

Thanks in advance,

Phil

Hi dh,

Thanks for the quick response...doesn't seem to work though!

I'm wondering if that's because the Split is a udf that hasn't been
defined?

Any thoughts would be appreciated!

Phil

Hi again dh

Forget my last post - it works!

For some reason all the macros stopped working in my file. I closed it
without saving, pasted your code in again and all was well.

Thank you very much for your time and effort.

best wishes,

Phil

Hey Phil - glad to see it worked! Have a great day.

David


"Philipgrae" wrote:

Hi again dh

Forget my last post - it works!

For some reason all the macros stopped working in my file. I closed it
without saving, pasted your code in again and all was well.

Thank you very much for your time and effort.

best wishes,

Phil