OfficeTalk: Working with Lists and Tables: VBA Samples (Part 1 of 2)

Article
10/16/2013

Summary: Tables and lists are an integral part of Microsoft Excel. Microsoft Excel MVP Ron de Bruin explains some handy VBA subroutines that can make you more productive when using tables or lists. (6 printed pages)

Ron de Bruin, Excel MVP

Frank Rice, Microsoft Corporation

April 2009

Applies to: Microsoft Excel 2003, Microsoft Office Excel 2007

Contents

Working with Tables and Lists in Excel
Checking to See Whether the Active Cell is in a Table or List
Copying a Table or List to a New Worksheet in the Current Workbook
Conclusion
Additional Resources

You can download a Microsoft Excel workbook containing the procedures discussed in these columns and more. To download this workbook, see VBA code examples for Tables in Excel 2007 or a List in Excel 2003.

Working with Tables and Lists in Excel

Tables and lists are excellent for organizing related data neatly into rows and columns. This makes the data easy to read, concise, and easy to filter. There are a number of benefits to putting your data into tables:

You can filter the data even if there are empty rows or columns.
Clicking into the table displays the filter arrows in the header row which give you options to sort and filter the data.
When you scroll past the column headers, the headers at the top of screen automatically switch to display the header names.
If you add a row after the last row or a column to the left of the last column in the table, the table expands to encapsulate the new row or column.
Charts or PivotTable dynamic views based on a table automatic update if the table size changes.
If you enter a formula in the first cell of the table row, the formula automatically fills down the entire column.
If you edit a formula in one field, all formulas in that row/column update automatically.

For more information about tables in Microsoft Office Excel 2007, see Overview of Excel tables.

In Excel 2007, it is simple to insert your data into a table:

On the Insert tab, in the Tables group, click the Table icon.
This displays the Create Table dialog box as shown in Figure 1. Here you specify the range of the data you want in the table. You can also specify whether your table has headers.

Figure 1. The Create Table dialog box
Place the cursor into any cell in the table and right-click. Notice that there are a number of operations you can perform on the table as shown in Figure 2. This includes:
- Insert additional columns and rows
- Sort the data
- Move the cell colors, font colors, or icons to the top of the table
- Convert the table to a range
- Change the formatting of the cells
Figure 2. The table context (right-click) menu offers several options for working with tables

There are also a number of things you can do with tables and list by using the VBA object model. In this column, I discuss some simple VBA procedures for working with tables and lists to include:

Checking to See Whether the Active Cell is in a Table or List
Copying a table or list to another worksheet

Checking to See Whether the Active Cell is in a Table or List

Many times before executing other commands and functions, you want to ensure that the active cell is in a table or list. The following code tests for this condition and displays a message box with the results.

Sub TestIfActiveCellIsInTable()
    Dim ActiveCellInTable As Boolean
    Dim ACell As Range

    'Set a reference to the ActiveCell named ACell. You can always use
    'ACell now to point to this cell, no matter where you are in the workbook.
    Set ACell = ActiveCell

    'Test whether ACell is in a table.
    On Error Resume Next
    ActiveCellInTable = (ACell.ListObject.Name <> "")
    On Error GoTo 0

    If ActiveCellInTable = True Then
        MsgBox "The ActiveCell is a part of a table."
    Else
        MsgBox "The ActiveCell is not a part of a table."
    End If
End Sub

If the active cell is in a table, you can use the following statement to point to the entire table.

ACell.ListObject.Range

You use the following statement to reference a table without header.

ACell.ListObject.DataBodyRange

Copying a Table or List to a New Worksheet in the Current Workbook

The following procedure copies only the visible cells in a table or list to a new worksheet. Like the previous procedure, it uses the ListObject object to represent the table or list. One additional test in this procedure is for the number of non-contiguous areas. Excel has a limit of 8192 non-contiguous areas that it can copy in any table. The macro prompts you whether you want to create a table of the new data on the new worksheet. If you cancel this dialog, you are then prompted if you want to copy the formats so that the normal range looks professional.

Sub CopyListOrTable2NewWorksheet()
'Works in Excel 2003 and Excel 2007. Only copies visible data.
    Dim New_Ws As Worksheet
    Dim ACell As Range
    Dim CCount As Long
    Dim ActiveCellInTable As Boolean
    Dim CopyFormats As Variant
    Dim sheetName As String

    'Check to see if the worksheet or workbook is protected.
    If ActiveWorkbook.ProtectStructure = True Or ActiveSheet.ProtectContents = True Then
        MsgBox "This macro will not work when the workbook or worksheet is write-protected."
        Exit Sub
    End If

    'Set a reference to the ActiveCell. You can always use ACell to
    'point to this cell, no matter where you are in the workbook.
    Set ACell = ActiveCell

    'Test to see if ACell is in a table or list. Note that by using ACell.ListObject, you
    'do not need to know the name of the table to work with it.
    On Error Resume Next
    ActiveCellInTable = (ACell.ListObject.Name <> "")
    On Error GoTo 0

    'If the cell is in a list or table run the code.
    If ActiveCellInTable = True Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        'Test if there are more than 8192 separate areas. Excel only supports
        'a maximum of 8,192 non-contiguous areas through VBA macros and manual.
        On Error Resume Next
        With ACell.ListObject.ListColumns(1).Range
            CCount = .SpecialCells(xlCellTypeVisible).Areas(1).Cells.Count
        End With
        On Error GoTo 0

        If CCount = 0 Then
            MsgBox "There are more than 8192 areas, so it is not possible to " & _
                   "copy the visible data to a new worksheet. Tip: Sort your " & _
                   "data before you apply the filter and try this macro again.", _
                   vbOKOnly, "Copy to new worksheet"
        Else
            'Copy the visible cells.
            ACell.ListObject.Range.Copy

            'Add a new Worksheet.
            Set New_Ws = Worksheets.Add(after:=Sheets(ActiveSheet.Index))

            'Prompt the user for the worksheet name.
            sheetName = InputBox("What is the name of the new worksheet?", _
                                 "Name the New Sheet")

            On Error Resume Next
            New_Ws.Name = sheetName
            If Err.Number > 0 Then
                MsgBox "Change the name of sheet : " & New_Ws.Name & _
                     " manually after the macro is ready. The sheet name" & _
                     " you typed in already exists or you use characters" & _
                     " that are not allowed in a sheet name."
                Err.Clear
            End If
            On Error GoTo 0

            'Paste the data into the new worksheet.
            With New_Ws.Range("A1")
                .PasteSpecial xlPasteColumnWidths
                .PasteSpecial xlPasteValuesAndNumberFormats
                .Select
                Application.CutCopyMode = False
            End With

            'Call the Create List or Table dialog.
            Application.ScreenUpdating = True
            Application.CommandBars.FindControl(ID:=7193).Execute
            New_Ws.Range("A1").Select

            ActiveCellInTable = False
            On Error Resume Next
            ActiveCellInTable = (New_Ws.Range("A1").ListObject.Name <> "")
            On Error GoTo 0

            Application.ScreenUpdating = False

            'If you do not create a table, you have the option to copy the formats.
            If ActiveCellInTable = False Then
                Application.GoTo ACell
                CopyFormats = MsgBox("Do you also want to copy the Formats?", _
                                     vbOKCancel + vbExclamation, "Copy to new worksheet")
                If CopyFormats = vbOK Then
                    ACell.ListObject.Range.Copy
                    With New_Ws.Range("A1")
                        .PasteSpecial xlPasteFormats
                        Application.CutCopyMode = False
                    End With
                End If
            End If
        End If

        'Select the new worksheet if it is not active.
        Application.GoTo New_Ws.Range("A1")

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With

    Else
        MsgBox "Select a cell in your list or table before you run the macro.", _
               vbOKOnly, "Copy to new worksheet"
    End If
End Sub

Conclusion

In the next column in this series, OfficeTalk: Working with Lists and Tables: VBA Samples (Part 2 of 2), I provide additional VBA methods that you can use to simplify your work with tables and lists such as copying a table or list to a new workbook or applying a filter.

Additional Resources

More information on the subjects described in this column can be found at the following locations.