In Excel 2007-2010 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-2010 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-2010 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-2010 and not available in List in Excel 2003.
Read also the information on the sites in the "More Information".


Download example files

On this page you can download a example workbook for Excel 2003 (List) and one
for Excel 2007-2010(Table) with a few code examples. The code in both files is the same.
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 on the worksheets and the comments in the code good.

Download 2007-2010 example workbook

Download 2003 example workbook

Examples in the file :

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

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


More Information

Overview of Excel tables
http://office.microsoft.com/en-us/excel/HA100485461033.aspx

Working with Lists and Tables: VBA Samples (Part 1 of 2)
http://msdn.microsoft.com/en-us/library/dd637097(office.11).aspx

Working with Lists and Tables: VBA Samples (Part 2 of 2)
http://msdn.microsoft.com/en-us/library/dd795215(office.11).aspx

Table Tools Add-in for Excel 2007-2010
http://www.rondebruin.nl/table.htm

Jan Karel Pieterse (MVP) :
http://www.jkp-ads.com/Articles/Excel2007Tables.asp
http://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp

Frank Rice (MSDN)
Bringing Improvements to Tables in Excel 2007 (Part 1 of 6)
Bringing Improvements to Tables in Excel 2007 (Part 2 of 6)
Bringing Improvements to Tables in Excel 2007 (Part 3 of 6)
Bringing Improvements to Tables in Excel 2007 (Part 4 of 6)
Bringing Improvements to Tables in Excel 2007 (Part 5 of 6)
Bringing Improvements to Tables in Excel 2007 (Part 6 of 6)