Section 2.9.Create a List

2.9. Create a List

In Microsoft Excel 2003, lists are ranges of cells that can easily be sorted, filtered, or shared. Lists are a little different from the AutoFilter feature available in earlier versions of Excel in that lists are treated as a single entity, rather than just a range of cells. This unity is illustrated by a blue border that Excel draws around the cells in a list (Figure 2-22).

There are other nice-to-have advantages to lists over AutoFilter ranges:

• Lists automatically add column headers to the range.

• Lists display a handy List Toolbar when selected.

• It is easy to total the items in a list by clicking the Toggle Total button.

• XML data can be imported directly into a list.

  Figure 2-22. A list (left) and an AutoFilter range (right)

  
  
  

• Excel can automatically check the data type of list entries as they are made.

• Lists can be shared and synchronized with teammates via Microsoft SharePoint Services.

That last item is the key advantage of listsreally, lists are just a way to share information that fits into columns and rows.

2.9.1. How to do it

To create a list from Excel, select a range of cells and then choose Data List Create List. Excel converts the selected range into a list and displays the Lists toolbar (shown in Figure 2-22).

To create a list from code, use the Add method of the ListObjects collection. 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 from the range A1:C3:

    Dim ws As Worksheet    Dim rng As Range    Set ws = ThisWorkbook.Sheets("Sheet1")    Set rng = ws.Range("A1:C3")    ws.ListObjects.Add xlSrcRange, rng

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:

    Set rng = ws.Range("A2:C4")    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:

    Set rng = ws.Range("A1:C4")    ws.ListObjects.Add xlSrcRange, rng, , xlYes

Since column headers and new rows added to a list cause the subsequent rows to shift down, it is a good idea to avoid placing data or other items in the rows below a list. If you do place items there, you receive a warning any time the list expands.

When creating lists in code, it is also a good idea to name the list so that subsequent references to the list can use the list's name rather than its index on the worksheet. To name a list, set the Name property of the ListObject :

    Dim lst As ListObject    Set rng = ws.Range("A1:C4")    Set lst = ws.ListObjects.Add (xlSrcRange, rng, , xlYes)    lst.Name = "Test List"

You can get a reference to a named list using the Worksheet object's ListObjects property:

    Set ws = ThisWorkbook.Worksheets("Test Sheet")    Set lst = ws.ListObjects("Test List")

2.9.2. How it works

If you omit the optional arguments with the ListObjects' Add method, Excel uses its own logic to determine the range of cells in the list and whether a list contains a header row. Basically, all contiguous cells containing data are assumed to be part of the list, and if the first row of data is text, that row is assumed to be a header row.

In general, it's not a good idea to deal with assumptions in codeit's better to be explicit so you know whether a new row was inserted for column headers and that inadvertent blank rows don't cause the list to omit subsequent data.

Another thing to remember is that sorting, filtering, and updating a list can shift or hide items on the worksheet that are not part of the list. It is not generally a good idea to place non-list items on the same rows as a list or below a list since those items may be moved, hidden, or overwritten in some situations.