Tables in Excel VBA – Explained with Examples!

Managing the data with Tables is very easy in Excel. We can do lot more things if your data is in the form of tables. In this section we will show some of the Tables operations using Excel VBA.

• Create Tables in Excel VBA
• Sorting Tables in Excel VBA
• Filtering Tables in Excel VBA
• Clear Toggle Table Filters in Excel VBA

Create Tables in Excel VBA:

Sometimes you may want to create Tables in Excel VBA. Please find the following code to Create Tables in Excel VBA.

• Solution
• Code
• Output
• Reset a Table back to Normal Range
• Example File

Create Tables in Excel VBA – Solution(s):

You can use ListObjects.Add method of sheet to create tables in excel VBA. We can create table in the following way. The following code creates a table “myTable1” and referring to sheet1 of a range(“$A$1:$D$10”) .

Code:

'Naming a range
Sub sbCreatTable()

    'Create Table in Excel VBA
    Sheet1.ListObjects.Add(xlSrcRange, Range("A1:D10"), , xlYes).Name = "myTable1"

End Sub

Output:

Instructions:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Double click on ThisWorkbook from Project Explorer
4. Copy the above code and Paste in the code window
5. Press F5
6. GoTo Sheet1 and Select Range A1 to D10
7. You should see the above output in Sheet1

Reset a Table back to Normal Range

If you want to Reset the table back to original range, you can use Unlist property of table object. Following code will show you how to remove table formats and reset to normal range.

Sub sbReset_Table_BackTo_Range()
    
    'Reset Table Back to Original Range
    On Error Resume Next  'If there are no Table ignore the below Statement
    Sheet1.ListObjects("myTable1").Unlist
    
End Sub

Example File

Download the example file and Explore it.
Analysistabs – Create Tables in Excel VBA

Sorting Tables in Excel VBA:

Examples for Sorting Table in Excel VBA with using sort method of ListObjects. You can learn how to sort table with examples.

• Solution
• Code
• Output

Sorting Table in Excel VBA – Solution(s):

You can use sort method of ListObjects for sorting table in Excel VBA. We can do sort data in the following way.

Code:

Sub sbSortTable()

     'Naming a range
     Sheet1.Sheets("Sheet1").ListObjects("myTable1").Sort.SortFields.Clear
     Sheet1.Sheets("Sheet1").ListObjects("myTable1").Sort.SortFields.Add Key:=Range("myTable1

					

						


					
				
[[#All],[EmpName]]"), SortOn:=sortonvalues, Order:=xlAscending, DataOption:=xlSortNormal
     Range("myTable1[#All]").Select
     With Sheet1.Worksheets("Sheet1").ListObjects("myTable1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Output:

Instructions:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Double click on ThisWorkbook from Project Explorer
4. Copy the above code and Paste in the code window
5. Press F5
6. GoTo Sheet1 and Select Range A1 to D10
7. You should see the above output in Sheet1

Filtering Tables in Excel VBA

Sometimes you may want to Filter Tables in Excel VBA. Please find the following code for Filtering Tables in Excel VBA.

• Solution
• Code
• Output
• Example File

Filtering Tables in Excel VBA – Solution(s):

You can use ListObjects(“TableName”).Range.AutoFilter method for Filtering tables in excel VBA. We can filter table in the following way. The following code filters a table “myTable1” and referring to sheet1 of a range(“$A$1:$D$10”).In this Case I am applying filter for second column and looking for description “DDD” in a table.

Code:

'Filtering a table
Sub sbFilterTable()

     ActiveWorkbook.Sheets("Sheet1").ListObjects("myTable1").Range.AutoFilter field:=2, Criteria1:="DDD" 'matched with 4 in column c2 records will be shown

End Sub

Output:

Instructions:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Double click on ThisWorkbook from Project Explorer
4. Copy the above code and Paste in the code window
5. Press F5 to execute Macro
6. GoTo Sheet1 and Select Range A1 to D10
7. You should see the above output in Sheet1

Example File

Download the example file and Explore it.
Analysistabs – Filtering Tables in Excel VBA

Clear or Toggle Table Filters in Excel VBA:

Examples for Clear Toggle Table Filters in Excel VBA with using FilterMode Property and AutoFilter method. You can learn how to Clear Toggle Table Filters in Excel VBA with following example.

• Solution
• Code
• Output
• Example File

Clear Toggle Table Filters in Excel VBA – Solution(s):

You can Clear Toggle Table Filters in Excel VBA with using FilterMode Property and AutoFilter method in Excel VBA. We can do Clear table filter in the following way.

Code:

'Clear Table Filter
Sub sbClearFilter()

    'Check Filter is Exists or Not
    If ActiveWorkbook.Sheets("Sheet1").FilterMode = True Then
    
        ActiveWorkbook.Sheets("Sheet1").ListObjects("myTable1").Range.AutoFilter
        
    End If

End Sub

Output:

Instructions:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Double click on ThisWorkbook from Project Explorer
4. Copy the above code and Paste in the code window
5. Press F5 to execute Macro
6. GoTo Sheet1 and check the Table Data from A1 to D10
7. You should see the above output in Sheet1

Example File

Download the example file and Explore it.
Analysistabs – Clear Tables in Excel VBA

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource

Excel VBA Reference	Project Management Reference
VBA Reference: VBA Code Explorer VBA Excel Application VBA Excel Workbook VBA Excel Worksheet VBA Excel Range VBA ActiveX Controls VBA Userforms VBA Projects Excel Reference: Excel Tutorial Microsoft Excel 2003 Microsoft Excel 2007 Microsoft Excel 2010 Microsoft Excel 2013 Microsoft Excel 2016	What is a Project? Project Appraisal Project Management Project Plan Project Resource What is Gantt Chart? Excel Templates Excel Project Management Templates PowerPoint Project Management Templates MS Word Project Management Templates Excel Basics Tutorials Excel VBA Tutorials Excel Functions and Formulas

External VBA Reference

Excel VBA Reference

Project Management Reference

VBA Reference: Getting Started with VBA in Office VBA for MS Office Application: Complete References Excel VBA Reference VBA Tutorials VBA Macros VBA Functions Excel Reference: Microsoft Excel Reference Excel Formulas Excel 2016 Excel Functions Excel Quick Tutorials

Project Management Methodologies MS Project Templates Excel, Word, PowerPoint Timeline Templates Excel, Word, PowerPoint Planners and Trackers Microsoft Excel Templates MS PowerPoint Excel Templates MS Word Templates MS Office Schedules Templates Excel, Word, PowerPoint Agenda Templates Excel, Word, PowerPoint Invoices Templates Excel, Word, PowerPoint Business Plans Templates Excel, Word, PowerPoint Financial Management Templates Excel, Word, PowerPoint Minutes Templates