Sorting multiple lists (dynamic ranges)

[dasadler]

Hello,

Sorry if this has been asked before - my forum search did not find it.

Using Excel 2007, I have a worksheet with about 30 lists (30 columns). Each list is defined as:

=Lists!$A$1:INDEX(Lists!$A:$A,COUNTA(Lists!$A:$A))
=Lists!$B$1:INDEX(Lists!$B:$B,COUNTA(Lists!$B:$B))
=Lists!$C$1:INDEX(Lists!$C:$C,COUNTA(Lists!$C:$C))
=Lists!$D$1:INDEX(Lists!$D:$D,COUNTA(Lists!$D:$D))
and so on

What i would like to do is for these lists to be sorted alphabetically any time any of the lists are changed (item added, deleted, or edited).

Maybe sort all the lists when the workbook is opened then sort only the list that has been changed... that sounds more efficient. Since none of the lists have more than 50 items, efficiency may not be a consideration.

I would appreciate any thoughts on this or code I can enter into the Lists! worksheet to make this happen.

Thank you.

 

[Peter_SSs]

You could try this worksheet change event code in a copy of your workbook. I have assumed headings in your lists. To implement ..
1. Right click the 'Lists' sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rCol As Range

  For Each rCol In Target.Columns
    With rCol.EntireColumn
      .Sort key1:=.Cells(1), Order1:=xlAscending, Header:=xlYes
    End With
  Next rCol
End Sub

 

[dasadler]

Peter, thank you for the reply. My lists do not have headings so how must I modify the code for that?.. change it to Header:=xlNo?

 

[Peter_SSs]

.. change it to Header:=xlNo?

Yes, do exactly that.

 

[dasadler]

Excellent - works like a charm. Thank you.

 

[Peter_SSs]

Excellent - works like a charm. Thank you.

Good news, and you're welcome.