ExcelMadeEasy: Vba tables in vba in Excel

Vba tables in vba in Excel

The Excel tables are accessed in VBA using the ListObjects Object.

The "Listobjects" returns a "Listobject" which represents an Excel Table.

However, "Listobjects" is used to access an already existing table.

Create Table

The following example shows, how to create a new table in excel.

Sub CreateNewTable()
Sheets("Q49").ListObjects.Add(xlSrcRange, Range("K1:N10"), , xlYes).Name = "myTable1"
End Sub

The above example creates a plain table as shown in the right.

Convert to Range

The following code shows how to convert the table to Normal range.

Sub tabletorange()
On Error Resume Next
Sheets("Q49").ListObjects("myTable1").Unlist
End Sub

Loop through Table

The following code shows loops through each Table column/row

Sub tableloop()
Dim tbl As ListObject
Dim x As Long
Set tbl = ActiveSheet.ListObjects("myTable1")
On Error Resume Next
'Loop Through Each Column in Table

For x = 1 To tbl.ListColumns.Count
tbl.ListColumns(x).Range.ColumnWidth = 8
Next x
'Loop Through Every Row in Table
For x = 1 To tbl.Range.Rows.Count
tbl.Range.Rows(x).RowHeight = 20
Next x
'Loop Through Each DataBody Row in Table
For x = 1 To tbl.ListRows.Count
tbl.ListRows(x).Range.RowHeight = 15
Next x
End Sub

Clear Filter in Table

The following code shows how to clear filter in Table headers

Sub sbClrFtr()
If Sheets("Q49").FilterMode = True Then
Sheets("Q49").ListObjects("myTable1").Range.AutoFilter
End If
End Sub

A screeenshot of the sample table is shown below

excel vba tables in vba

You can find similar Excel Questions and Answer hereunder

1) How can I loop through all WorkSheets and get their names using VBA?

2) How can I find the number of working days between 2 dates using VBA?

3) How to print a worksheet in Excel VBA

4) How can I add a legend to a chart using VBA?

5) Excel 2010 vba replacement of application filesearch in Excel

6) How do I restrict user entry in ActiveX Text Box to just numeric values?

7) How do I enter a formula in a cell using VBA (using Absolute Reference)?

8) How to hide and unhide rows and columns in excel VBA

9) How can I export a WorkSheet as a PDF using VBA?

10) How can I turn off Alerts using VBA?

Here the previous and next chapter