"Create List..." in Excel 2003 with VBA

While working on data within Excel with VBA, I like to use dynamic lists to address the data and eventually to give it a name.

To create a list, the same one as you would do manually by calling "Create List..." from the context menu, you can execute the VBA script below.

Private Sub CreateList()

    Range("A1").Select
    
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    
    Dim NumberOfColumns As Integer
    Dim NumberOfRows As Integer
    Dim StartCell As Range
    Dim EndCell As Range
    
    NumberOfColumns = ActiveSheet.UsedRange.Columns.Count
    NumberOfRows = ActiveSheet.UsedRange.Rows.Count
    
    Set StartCell = Cells(1, 1)
    Set EndCell = Cells(NumberOfRows, NumberOfColumns)
    
    Dim DataRange As Range
    Set DataRange = Range(StartCell, EndCell)
    
    ActiveSheet.ListObjects.Add(xlSrcRange, DataRange, , xlYes).Name = "List1"
    Range(StartCell, EndCell).Select
    ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:=DataRange
    Range("Data").Select

End Sub

I also give the list the name "Data", which I do to access the data by name afterwords, but this is not required to create the list.

Enjoy :)