Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

VBA code examples for a List or Table in Excel 2003-2016

In Excel 2007-2016 we have an option in the Ribbon on the Insert tab named Table. This option also exists in Excel 2003 but with the name List and it's found under the Data menu in Excel 2003.

How do I convert my range to a Table?

If you select a cell in your normal range and press the Table button it will show you this dialog. In Excel 2003 you can right click on a cell in your range and choose "Create List" or use Data>List>Create List in the menu.

There are many benefits when you use Tables instead of a normal range in your worksheet. After you defined the Table in Excel 2007-2016 you will have access to a Ribbon tab named Table Tools with:

Table Styles gallery for different color schemes(with live preview)
Add a total row with one click
Option to remove duplicates
External data options

A few other benefits if your data is in a Table in Excel 2007-2016 are:

No problem if there is a empty row or column when your filter your Table. It will automatic turn on AutoFilter in the Table so you can use the AutoFilter dropdowns to sort or Filter. When you scroll past the column headers, the headers at the top of screen will automatically switch to display the header names. If you add a record below the last row in the Table or add a column to the right or left then the Table will automatic expend to include the new row or column. Charts or Pivot Tables based on a Table will automatic update if you’re Table size change. If you enter a formula in the first cell of the Table row it will automatic fill down the whole field. If you edit one formula in a field all formulas in that field will update automatic.

Note: Most benefits are new in Excel 2007-2016 and not available in List in Excel 2003.

 

Download example files

On this page you can download a example workbook for Excel 2003 (List) and one for Excel 2007-2016(Table) with a few code examples. All macros in the example workbook are working without changing the code. Some code will need to be edited if you use it in your own files, but the changes shouldn’t be too difficult.

Note: Read the information good on the worksheets and also the comments in the code.

Download 2007-2016 example workbook

Download 2003 example workbook

Examples in the files :

1) Copy visible data from the Table/List to a new worksheet
2) Copy visible data from the Table/List to a new workbook
3) Filter in Place with VBA code
4) Filter and copy the visible data to a new worksheet
5) Filter and copy the visible data to a new workbook
6) Copy the data for every unique value to own worksheet
7) Copy the data for every unique value to own workbook

 

Download add-in for Excel 2007-2016

Check out also my Table Tools add-in for Excel 2007-2016.
You can find most macro options and a lot more in this add-in.