renaming excel data lists

[c1802362]

Excel 2003 has the Data/Lists/Create Lists functionality. Creating
lists is no problem, but I'm at a loss as to renaming any lists I
create. Excel automatically names the lists in the file as 1, 2, 3...
It would be nice to be able to rename them as desired.

I've been using a workaround of adding a named range over the top of
the list.

example: ActiveWorkbook.Names.Add Name:="AddressList",
RefersToR1C1:= range

Obviously, this is clumsy. Is there a better way?

so how do I change

Set rng = ws.ListObjects(1).ListColumns(3).Range

to something like

Set rng = ws.ListObjects("AddressList").ListColumns
(3).Range


Art

 

[Gord Dibben]

With a list of your desired names in H1:H10

Sub Rename_Lists()
Dim lst As ListObject
Dim rng As Range
Dim i As Integer
On Error GoTo endit
Set rng = Range("H1")
For Each lst In ActiveSheet.ListObjects
lst.Name = rng.Offset(i, 0).Value
i = i + 1
Next lst
Exit Sub
endit:
MsgBox "there is a List by that name, re-type a name"
End Sub

To rename just a single List.

Sub rename_one_list()
ActiveSheet.ListObjects("List1").Name = "AddressList"
End Sub


Gord Dibben MS Excel MVP

 

[c1802362]

Thanks!

 

[Gord Dibben]

You're welcome.

Gord

Thanks!