How to Insert Table in Excel using VBA Easily (7 Examples)

Welcome to Excel Avon

Insert Table in Excel using VBA

DOWNLOAD USED EXCEL FILE FROM HERE>>

Today’s article is about Insert table in Excel, today we are going to understand how to Insert table in Excel using VBA, By the way, we know how to insert table in excel, but today’s article will learn to insert table using VBA. we can automate our task with help of codes and codes that will manipulate (like inserting, creating, or deleting a row, column, or graph) the data in a worksheet or workbook. With the help of VBA, we can also automate the task in excel to perform all these tasks we need to insert table and run the VBA code properly which we will discuss in this article. 

We have some data in which we will insert table with the help of VBA-

insert-table-in-excel-using-VBA

How to Insert Table in Excel using VBA?

We have to go like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below. 

 Insert table in excel VBA

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

 Insert table in excel VBA

First, we will write the ‘Insert Table’ subroutine after inserting the module in VBE.

Sub InserTable()

End Sub

After Subroutine for InsertTable we will Define variable for ‘ListObject’.

Sub InsertTable()
Dim Tb1 as ListObject

End Sub

Define variable for ‘Worksheet’.

Sub InsertTable()
Dim Tb1 as ListObject
Dim ws as Worksheet

End Sub

Define variable for ‘Long (Last Row)’. With this we will define the variable for the range

Sub InsertTable()
Dim Tb1 as ListObject
Dim ws as Worksheet
Dim Lr as Long
Dim Rng as Range

End Sub

Now we will set Active Sheet

Sub InsertTable()
Dim Tb1 as ListObject
Dim ws as Worksheet
Dim Lr as Long
Dim Rng as Range

Set WS = ActiveSheet
End Sub

In the worksheet we will define the last row of Data.

Sub InsertTable()
Dim Tb1 as ListObject
Dim ws as Worksheet
Dim Lr as Long
Dim Rng as Range

Set WS = ActiveSheet
Lr = ws.Range("A" & Rows.Count). End(xlUp).Row
End Sub

Now we will set the range A1 to the last row of the column C

Sub InsertTable()
Dim Tb1 as ListObject
Dim ws as Worksheet
Dim Lr as Long
Dim Rng as Range

Set WS = ActiveSheet
Lr = ws.Range("A" & Rows.Count). End(xlUp).Row
Set Rng = WS.Range("A1:C" & Lr)
End Sub

Now we will add ListObject to Table 1, The first parameter we need is xlSrcRange for SourceType, second parameter is Source in which we will write range and select yes in header parameter Because we have header in our data.

Sub InsertTable()
Dim Tb1 as ListObject
Dim ws as Worksheet
Dim Lr as Long
Dim Rng as Range

Set WS = ActiveSheet
Lr = ws.Range("A" & Rows.Count). End(xlUp).Row
Set Rng = WS.Range("A1:C" & Lr)
Set Tb1 = WS.ListObject.Add(xlSrcRange, Rng,,xlYes)
End Sub

Now we will go to the worksheet and then run the macro

Insert table in excel VBA

As you can see the Insert table in Excel worksheet.

insert-table-in-excel-using-VBA2

After the table is inserted, we will go back to VBE and then edit the inserted table by writing a separate subroutine for editing.  With this we will also define the variable.

Sub InsertTable()
Dim Tb1 as ListObject 
Dim ws as Worksheet 
Dim Lr as Long 
Dim Rng as Range 

Set WS = ActiveSheet 
Lr = ws.Range("A" & Rows.Count). End(xlUp).Row

Set Rng = WS.Range("A1:C" & Lr)

Set Tb1 = WS.ListObject.Add(xlSrcRange, Rng,,xlYes) 
End Sub

Sub EditTable()
Dim Tb1 as ListObject 
Dim ws as Worksheet 

End Sub

Now we will set ActiveSheet and we will add table2 as ListObject.(We can Also put Index)

Sub InsertTable()
Dim Tb1 as ListObject 
Dim ws as Worksheet 
Dim Lr as Long 
Dim Rng as Range 

Set WS = ActiveSheet 
Lr = ws.Range("A" & Rows.Count). End(xlUp).Row

Set Rng = WS.Range("A1:C" & Lr)

Set Tb1 = WS.ListObject.Add(xlSrcRange, Rng,,xlYes) 
End Sub

Sub EditTable()
Dim Tb1 as ListObject 
Dim ws as Worksheet 

Set WS = ActiveSheet
Set Tb1 = WS.ListObject("Table2") 
End Sub

Changing the data of Column 2nd cell of the first row, replace by this date(12-dec-2002)

Sub InsertTable()
Dim Tb1 as ListObject 
Dim ws as Worksheet 
Dim Lr as Long 
Dim Rng as Range 

Set WS = ActiveSheet 
Lr = ws.Range("A" & Rows.Count). End(xlUp).Row

Set Rng = WS.Range("A1:C" & Lr)
Set Tb1 = WS.ListObject.Add(xlSrcRange, Rng, ,xlYes) 
End Sub

Sub EditTable()
Dim Tb1 as ListObject 
Dim ws as Worksheet 

Set WS = ActiveSheet
Set Tb1 = WS.ListObject("Table2") 
tb1.DataBodyRange.Cell(1,2) = #12-Dec-2002#
End Sub

After this we will Click Run button

insert-table-in-excel-using-VBA3

The cell of the worksheet has Edited after running Code

insert-table-in-excel-using-VBA4

Now I will add Auto Filter button, For Auto filter button select true for add button and False for remove Button.

Sub InsertTable()
Dim Tb1 as ListObject 
Dim ws as Worksheet 
Dim Lr as Long 
Dim Rng as Range 

Set WS = ActiveSheet 
Lr = ws.Range("A" & Rows.Count). End(xlUp).Row 

Set Rng = WS.Range("A1:C" & Lr) 
Set Tb1 = WS.ListObject.Add(xlSrcRange, Rng, ,xlYes) 
End Sub

Sub EditTable() 
Dim Tb1 as ListObject 
Dim ws as Worksheet 

Set WS = ActiveSheet 
Set Tb1 = WS.ListObject("Table2") 
tb1.ShowAutoFilter = False
End Sub

After click Run button, Filter buttons are removed from header. Now we can add filter button by setting true.

insert-table-in-excel-using-VBA5

You can remove header by setting header to false, if set true for header, header will be added.

Sub InsertTable() 
Dim Tb1 as ListObject 
Dim ws as Worksheet 
Dim Lr as Long 
Dim Rng as Range 

Set WS = ActiveSheet 
Lr = ws.Range("A" & Rows.Count). End(xlUp).Row
Set Rng = WS.Range("A1:C" & Lr) 

Set Tb1 = WS.ListObject.Add(xlSrcRange, Rng, ,xlYes) 
End Sub 

Sub EditTable() 
Dim Tb1 as ListObject 
Dim ws as Worksheet

Set WS = ActiveSheet 
Set Tb1 = WS.ListObject("Table2")
 
tb1.ShowAutoFilter = True
tb1.ShowHeaders = False
End Sub

Click Run Button

insert-table-in-excel-using-VBA3

As soon as we clicked the run button, the headers of the data given in the worksheet have been removed.

insert-table-in-excel-using-VBA6

Now we will add row of total

Sub InsertTable() 
Dim Tb1 as ListObject 
Dim ws as Worksheet 
Dim Lr as Long 
Dim Rng as Range 

Set WS = ActiveSheet 
Lr = ws.Range("A" & Rows.Count). End(xlUp).Row 
Set Rng = WS.Range("A1:C" & Lr) 
Set Tb1 = WS.ListObject.Add(xlSrcRange, Rng, ,xlYes) 
End Sub

Sub EditTable() 
Dim Tb1 as ListObject 
Dim ws as Worksheet
 
Set WS = ActiveSheet 
Set Tb1 = WS.ListObject("Table2")
 
tb1.ShowAutoFilter = True 
tb1.ShowHeaders = True 
tb1.ShowTotals = True 
End Sub

After written the code, Click Run Button

insert-table-in-excel-using-VBA3

Header, Filters and row of totals have been added

insert-table-in-excel-using-VBA7

Now I will add a row in table

Sub InsertTable() 
Dim Tb1 as ListObject 
Dim ws as Worksheet 
Dim Lr as Long 
Dim Rng as Range 

Set WS = ActiveSheet 
Lr = ws.Range("A" & Rows.Count). End(xlUp).Row 
Set Rng = WS.Range("A1:C" & Lr) 
Set Tb1 = WS.ListObject.Add(xlSrcRange, Rng, ,xlYes) 
End Sub 

Sub EditTable() 
Dim Tb1 as ListObject 
Dim ws as Worksheet 

Set WS = ActiveSheet 
Set Tb1 = WS.ListObject("Table2")
tb1.ListRow.Add 
End Sub

After written the code, Click Run Button

insert-table-in-excel-using-VBA3

Now you can see we have added one more row in the table but if you want to add after a particular cell then you can give the location of the cell.

insert-table-in-excel-using-VBA8

Similarly, we can also Delete row

Sub InsertTable() 
Dim Tb1 as ListObject 
Dim ws as Worksheet 
Dim Lr as Long 
Dim Rng as Range 

Set WS = ActiveSheet 
Lr = ws.Range("A" & Rows.Count). End(xlUp).Row 
Set Rng = WS.Range("A1:C" & Lr) 

Set Tb1 = WS.ListObject.Add(xlSrcRange, Rng, ,xlYes) 
End Sub 

Sub EditTable() 
Dim Tb1 as ListObject 
Dim ws as Worksheet 

Set WS = ActiveSheet 
Set Tb1 = WS.ListObject("Table2") 
tb1.ListRow(tb1.ListRows.count).Delete
End Sub

After written the code, Click Run Button

insert-table-in-excel-using-VBA3

As we had added the row, the row has been deleted as soon as the code runs.

insert-table-in-excel-using-VB9

With this, we have learned how to insert table in Excel sheet and edit a table.

So, I hope you have understood How to Insert Table in Excel using VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.  

DOWNLOAD USED EXCEL FILE FROM HERE>>

      LEARN MORE TOPIC IN VBA HERE 

You can also see well-explained video here about Insert Table in Excel using VBA

Leave a Reply