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.
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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Double click on ThisWorkbook from Project Explorer
- Copy the above code and Paste in the code window
- Press F5
- GoTo Sheet1 and Select Range A1 to D10
- 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.
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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Double click on ThisWorkbook from Project Explorer
- Copy the above code and Paste in the code window
- Press F5
- GoTo Sheet1 and Select Range A1 to D10
- 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.
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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Double click on ThisWorkbook from Project Explorer
- Copy the above code and Paste in the code window
- Press F5 to execute Macro
- GoTo Sheet1 and Select Range A1 to D10
- 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.
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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Double click on ThisWorkbook from Project Explorer
- Copy the above code and Paste in the code window
- Press F5 to execute Macro
- GoTo Sheet1 and check the Table Data from A1 to D10
- You should see the above output in Sheet1
Example File
Download the example file and Explore it.
Analysistabs – Clear Tables in Excel VBA
Premium Project Management Templates
50+ Professional Project Management Templates!
A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.
LIMITED TIME OFFER
50+ Project Management Templates Pack
Excel PowerPoint Word
Ultimate Project Management Template – Advanced
Excel Template
ULTIMATE PROJECT MANAGEMENT TEMPLATE
Excel Template
20+ Excel Project Management Pack
Excel Templates
20+ PowerPoint Project Management Pack
PowerPoint Templates
10+ MS Word Project Management Pack
Word Templates