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-
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.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
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
As you can see the Insert table in Excel worksheet.
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
The cell of the worksheet has Edited after running Code
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.
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
As soon as we clicked the run button, the headers of the data given in the worksheet have been removed.
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
Header, Filters and row of totals have been added
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
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.
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
As we had added the row, the row has been deleted as soon as the code runs.
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>>
You can also see well-explained video here about Insert Table in Excel using VBA
