Skip to content

VBA Tables and ListObjects

Associated Files Download Links

VBA Tables and ListObjects

This VBA tutorial will show you how to work with Tables and ListObjects. Tables are one of Excel’s most useful and powerful features, so in this tutorial, we will go over how to use VBA to create a table, add a simple sort to a table, filter a table and perform other table-related tasks.

Create a Table With VBA

The ListObjects.Add Method can add a table to a worksheet, based on a range in that worksheet. We have the range shown in ($A$1:$B$8) on a worksheet called Sheet1.

Range in a Worksheet in Excel

The following code will add a table, called Table1 to your worksheet, based on the range ($A$1:$B$8) using the default Table Style:

The result is:

Table Created in VBA

Inserting a Column at the End of the Table with VBA

You can use the ListColumns.Add method in order to add a column to the end of your table. We have our table called Table1 shown below.

Table Created in VBA

You can add a column to your table using the following code, which will always add a column to the end of the table:

The result is:

Adding a Column to a Table in Excel Using VBA

Inserting a Row at the Bottom of the Table with VBA

You can use the ListRows.Add method to add a row to the bottom of your table. We have our table called Table1 shown below.

Table Created in VBA

The following code will always add a row to the bottom of your table.

The result is:

Adding a Row to the Bottom of the Table

Adding a Simple Sort with VBA

You can sort a table with VBA. We have our table called Table1 shown below and we can use VBA to sort the Sales Column from lowest to highest.

Table Created in VBA

The following code will sort the Sales column in ascending order.

The result is:

Sorting a Table Using VBA

Filter a Table With VBA

You can also filter an Excel table using VBA. We have our table called Table1 and we would like to filter the table so that only sales of greater than 1500 are shown.

Table Created in VBA

We can use the Autofilter method, which has five optional parameters. Since we’d like to filter the Sales column which is the second column we set the Field to 2, and we use the xlAnd operator parameter, which is used for dates and numbers.

The result is:

Add a Filter to a Table

Clear the Filter with the ShowAllData Method in VBA

You can access the ShowAllData Method of the Worksheet class in order to clear the filter. If it’s a table’s filter(s) that you want to clear, then you first have to select a cell in the table, which you can do in VBA.

The ShowAllData method will generate an error if one does not use conditional logic in order to check if there has been a filter applied in the worksheet. The following code shows you how to do this:

Clear All Filters From An Excel Table

You can access the ShowAllData Method of the ListObject class without having to select a cell in the table first. The following code shows you how to do this:

Deleting A Row With VBA

You can delete a row in the databody of your table using the ListRows.Delete method. You have to specify which row using the row number. We have the following table called Table1.

Table Created in VBA

Let’s say you wanted to delete the second row in the databody of your table, the following code would allow you to do this:

The result is:

Deleting a Row Using VBA

Deleting a Column With VBA

You can delete a column from your table using the ListColumns.Delete method. We have the following table called Table1 shown below:

Table Created in VBA

In order to delete the first column, you would use the following code:

The result is:

Delete a Column with VBA

Converting a Table Back to a Range in VBA

You can convert a table back to a normal range using VBA. The following code shows you how to convert a table called Table1 back to a range:

Adding Banded Columns and formatting to all the Tables in a Worksheet using VBA

You can access all the tables in your worksheet using the ListObjects collection. In the sheet below we have two tables and we would like to add a Banded Column to both the tables at once and change the font of the data section of both tables to bold, using VBA.

Two Tables in Excel on the Same Worksheet

The result is:

Adding Banded Columns And Changing The Font of The Table  to Bold

Creating a Table in Access in VBA Using DoCmd.RunSQL

One of the main ways to create a table in Access in VBA, is through using the DoCmd.RunSQL method to run an action query with a SQL statement.

We have a button on our sample form and when we click on the button we’d like to create a table called ProductsTable with two fields or columns, one would be the primary key field called ProductsID and the other would be a field called Sales.

Create the Products Table in Access

In order to create this table we would use the following code:

The result is:

Create a Table in Access VBA

Filtering a Table in Access Using VBA

You can also filter a table in Access using the DoCmd.ApplyFilter method. We have our simple table shown below in Access called ProductsTable.

We would like to press this button on our form and then only see Sales that are greater than 1500.

Filtering a Table in Access

So, we would use the following code to do this:

The result is:

Filtered Access Table

You may also like some of this related content...

In VBA, you can create AutoFilter in order to filter a range of cells or
In VBA, you can create a Spin button which allows a user to increment a
In VBA, you can create an Option button to enable a user to choose from
In VBA, you can create a CheckBox where a user can check or uncheck the
In VBA, you can create a Listbox where a user can choose one of the
Advertisements
Automate Excel
Left Menu Icon