Tables in Excel VBA – Explained with Examples!

Home/Excel VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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:
  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.
mongopono.ru – 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:
  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.

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.
mongopono.ru – 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:
  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.
mongopono.ru – Clear Tables in Excel VBA

LIMITED TIME OFFER
By |July 3rd, 2013|Excel VBA|10 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

10 Comments

  1. Bruce January 13, 2014 at 2:20 AM - Reply

    Excellent! Now the couple pieces missing:

    1) Copying the filtered results to a new sheet Sheet02 (and whether or not the copied area is a range, table or neither at that time)
    2)Applying the same process to a new range of data and adding that filtered section to the previously established data on the new sheet Sheet02
    3) Sorting the new sheet data every time data is added and removing duplicates based upon one field being used as a key.

    I am sure I will run into all this, but you folks were off to such a great start of the information being clear and collected in one place, I wanted to place the suggestion, provided you ever have the time!

    • PNRao January 13, 2014 at 9:14 PM - Reply

      Hi Bruce,

      Thanks for your suggestion, I will add these in couple of weeks.

      Thanks-PNRao!

  2. Nurit May 27, 2014 at 10:11 PM - Reply

    How do you create a table when the range is dynamic. I want to automate the process of refreshing and opening pivot tables. I need to:
    1) import data from a software package (this will be done manually)
    2) open the file created in #1 and create and name table. The range of data is unknown.
    3) open the pivot table which will refresh automatically using the named table created in #2.

    I got this far – how do I change the defined range to a dynamic range and do I need the last line?

    Sub opengetfile()
    Dim strFileName As String
    Dim rgData As Range
    strFileName = Application.GetOpenFilename
    If strFileName = “False” Then Exit Sub
    MsgBox strFileName
    Workbooks.Open (strFileName)

    ActiveSheet.ListObjects.Add(xlSrcRange, Range(“$A$1:$AX$224”),, xlYes).Name = “Loans”
    Range(“Loans[#All]”).Select

    End Sub

    • PNRao May 28, 2014 at 11:45 PM - Reply

      Hi Nurit,

      Welcome to ANALYSITABS!

      You can create the dynamic range by finding the last row:
      (examples shown here: http://mongopono.ru/excel-vba/finding-last-used-row-with-data-worksheet/)

      Now your code will be:

      lastRow=200 ‘ This is Dynamic in your case, please use the example in the above link to find last row
      ‘Now Your range will be:
      ActiveSheet.ListObjects.Add(xlSrcRange, Range(“$A$1:$AX$″ &lastRow),, xlYes).Name = “Loans”

      ‘Now create the pivot table
      (examples shown here: http://mongopono.ru/excel-vba/pivot-tables-examples/ )

      Hope this helps!
      Thanks-PNRao!

    • Gejza Horvath June 16, 2014 at 1:42 AM - Reply

      If all the data on this sheet will be inside a listobject, you can use ActiveSheet.UsedRange property. Instead the Range() object.

  3. JonasTiger December 29, 2014 at 3:44 AM - Reply

    Hi
    Filtering Tables in Excel VBA…
    I need to filter my table everytime data changes.
    In your example, you filter “DDD”.
    In mine, I want filter all options except zero/blank cells of a range, i.e., its like excluding “DDD” and leave remaining data.
    please help me changing code for that.
    Thank you

  4. Amy January 30, 2015 at 5:27 AM - Reply

    This has been very helpful! I needed to create a macro to turn a dynamic range into a table, sort by a specific column, & filter the table based on that same column name. This page had all the information I needed.
    I look forward to checking out the other areas of your site.

    Thanks for sharing.

  5. yonsebastian June 1, 2015 at 5:52 AM - Reply

    working for year with VBA now 2013 comes with tables which is awesome!

    • PNRao June 1, 2015 at 12:20 PM - Reply

      Yes, Tables or ListObjects in Excel are very handy to deal with the data. We can perform many operations and its fast.

      Thanks-PNRao!

  6. Agni June 10, 2016 at 12:31 PM - Reply

    Hi,
    How to create a table without default auto filter and header.

Leave A Comment


Related pages


xlup vbahow to turn on macro in excelredim vbaexcel 2013 create macroexcel create macrosmultiple if functions in excel 2010excel 2013 column widthexcel vba cells.findexcel tasks templateleft excel vbavba debug printexcel formulas for namessql in sasvba load excel filedelete blanksdelete column excelms access import excel vbahow to unprotect excel sheet 2007vba trimexcel vba vlookup functionexcel match typeconditional statement worksheetexcel vba macros tutorialexcel worksheet hiddenclose word document vbahow to open xlsm file in excel 2003close word document vbainsert excel buttonvba sheet1convert excel csvvba vbyesnousing sumifs in excelms access docmd findrecordunprotect excel workbookdashboards with exceldelete sheet rowsexcel macro to open a filecost estimate spreadsheet templatecase syntax vbaiserror vlookup excelexcel function in vbamicrosoft excel dashboard templatesunprotect sheet excel 2010excel if clauseexcel activeworkbook saveasdaily scrum meeting templatevba gotoprogram vbaremove duplicate in excel 2003vba string to integervba code to open excel file from folderexcel macro to hide columnsexcel vba functions tutorialexcel macro tipsexcel vba inputbox cancelexcel vba codeadvanced excel vba examplesproject management timeline template excelequals vbavba sql accessconsolidate function in excelhow do i remove blank rows in excel 2010project deliverables template excelexcel if statement multiple conditionsvba coding tutorialddl queryexcel pivot table auto refreshworksheet function vbaqc checklist templatesql certification questions and answers pdfinstr function vbacreating a pie chart in excel 2007vba save filelist box controlxls macro tutorialexcel 2007 pivot table tutorialaverageifs