Programming Excel with VBA and .NET

Create a List in Code

Use the Add method of the ListObjects collection to create a list in code. The ListObjects collection is exposed as a property of the Worksheet object. The following code creates a new list for all the contiguous data starting with the active cell:

ActiveWorksheet.ListObjects.Add

Use the Add method’s arguments to create a list out of a specific range of cells. For example, the following code creates a list out of the range A2:D5:

Sub CreateList1(  )
    Dim ws As Worksheet, rng As Range
    Set ws = ActiveSheet
    Set rng = ws.Range("A2:D5")
    ws.ListObjects.Add xlSrcRange, rng
End Sub

When Excel creates the preceding list, it automatically adds column headings to the list either by converting the first row into column headings or by adding a new row and shifting the subsequent data rows down. It’s hard to know exactly what will happen because Excel evaluates how the first row is intended. You can avoid this assumption by supplying the HasHeaders argument, as shown here:

ws.ListObjects.Add xlSrcRange, rng, , xlNo

Now, the preceding code adds headers to row 2 and shifts the range down one row.

Lists always include column headers. To avoid shifting the range down one row each time you create a list, include a blank row at the top of the source range and specify xlYes for HasHeaders as shown here:

Sub CreateList2(  )
    Dim ws As Worksheet, rng As Range
    Set ws = ActiveSheet
    Set rng = ws.Range("A1:D5")
    ' Use first row as headers.
    ws.ListObjects.Add xlSrcRange, rng, , xlYes
End Sub

Since ...