Using a worksheet change macro to sort a bunch of lists

[Philipgrae]

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

 

[dhstein]

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]

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

 

[Philipgrae]

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

 

[dhstein]

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

David