|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here.|
OfficeTalk: Working with Lists and Tables: VBA Samples (Part 1 of 2)
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
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
Applies to: Microsoft Excel 2003, Microsoft Office Excel 2007
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.
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
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.
You use the following statement to reference a table without header.
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
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.
More information on the subjects described in this column can be found at the following locations.