Excel VBA to Create Table from Range (6 Examples)

While working with Microsoft Excel, we may utilize examining large quantities of data. And, transforming those data ranges into a table is one of the greatest options. Excel tables allow us to rapidly sort and filter the data, add new records, and instantly update charts and PivotTables. And Excel VBA helps the user to customize the application with just some simple codes. In this article, we will see some examples of Excel VBA to create a table from range.

Create a Table from Range in Excel VBA: 6 Examples

To convert range into a table using VBA is easier than using the ribbon.

Suppose you have a simple dataset with some items in column B, the items’ quantity in column C, and total sales for each item in column D.

Choose ListObjects.Add to turn a range into an Excel table.

The criteria for .Add are the following:

Use the SourceType xlSrcRange.

Example 1 – Create a Table from Range Using Excel VBA

STEPS:

• Go to the Developer tab on the ribbon.
• Click on Visual Basic to open Visual Basic Editor or press Alt + F11. You can also right-click on your sheet and select View Code.

• Go to Insert and select Module. The visual basic window will open.

• Create a Module, copy and paste the VBA code below.

VBA Code:

Sub Create_Table()
    Sheet1.ListObjects.Add(xlSrcRange, Range("B4:D9"), , xlYes).Name = "Table1"
End Sub

• Run the code by clicking on the RunSub button or pressing F5.

Don’t change the code. Change the range only.

• A table will be created from range B4:D9.

Sub Create_Table()

Sheet1.ListObjects.Add(xlSrcRange, Range("B4:D9"), , xlYes).Name = "Table1"

End Sub

Read More: How to Create a Table with Headers Using Excel VBA

Example 2 – Create a Table from Range Using Excel VBA

STEPS:

• Go to the Developer tab on the ribbon.
• Click on Visual Basic to open Visual Basic Editor or press Alt + F11. You can also right-click on the sheet and select View Code.
• Go to Insert and select Module. The visual basic window will open.
• Copy and paste the VBA code below.

VBA Code:

Sub Generate_Table()
    Dim tb2 As Range
    Dim wsht As Worksheet
    Set tb2 = Range("B4").CurrentRegion
    Set wsht = ActiveSheet
    ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=tb2).Name = "Table2"
End Sub

• Press F5 or click Run Sub to run the code.

Dim tb2 As Range
Dim wsht As Worksheet

Set tb2 = Range("B4").CurrentRegion
Set wsht = ActiveSheet

wsht.ListObjects.Add(SourceType:=xlSrcRange, Source:=tb2).Name = "Table2"

Example 3 – Create a Table from Range with VBA in Excel

STEPS:

• Select the whole range that you want to convert into a table.
• Click the Developer tab on the ribbon.
• Click on Visual Basic to open the Visual Basic Editor or press Alt + F11. You can also right-click on the sheet and select View Code.
• Go to Insert and select Module. The visual basic window will open.
• Copy and paste the VBA code below.

VBA Code:

Sub Create_Table3()
    Dim r As Range
    Dim wsht As Worksheet
    Dim tb3 As ListObject
    Set r = Selection.CurrentRegion
    Set wsht = ActiveSheet
    Set tb3 = wsht.ListObjects.Add(SourceType:=xlSrcRange, Source:=r, XlListObjecthasheaders:=x1Yes)
End Sub

• Press F5 to run the code.

• And, this will create a table from the data range as we got in Method 1.

Example 4 – Use VBA to Create a Dynamic Table from Rangecode

STEPS:

• Open the ribbon and select the Developer tab.
• Click on Visual Basic to open the Visual Basic Editor or press Alt + F11. You can also right-click on the sheet and select View Code.
• Go to Insert and select Module. The visual basic window will open.
• Copy and paste the following VBA code.

VBA Code:

Sub Create_Dynamic_Table1()
    Dim tbOb As ListObject
    Dim TblRng As Range
    With Sheets("Example4")
        lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        lLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set TblRng = .Range("A1", .Cells(lLastRow, lLastColumn))
        Set tbOb = .ListObjects.Add(xlSrcRange, TblRng, , xlYes)
        tbOb.Name = "DynamicTable1"
        tbOb.TableStyle = "TableStyleMedium14"
    End With
End Sub

• Press F5 to run the code.

Sub Create_Dynamic_Table1()

Dim tbOb As ListObject
Dim TblRng As Range

With Sheets("Example4")

lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set TblRng = .Range("A1", .Cells(lLastRow, lLastColumn))

Set tbOb = .ListObjects.Add(xlSrcRange, TblRng, , xlYes)

tbOb.Name = "DynamicTable1"

tbOb.TableStyle = "TableStyleMedium14"

Example 5 – Create a Dynamic Table from Range

STEPS:

• Open the ribbon and select the Developer option.
• Click on Visual Basic to open the Visual Basic Editor or press Alt + F11. You can also right-click on the sheet and select View Code.
• Go to Insert and select Module. The visual basic window will open.
• Copy and paste the following VBA code.

VBA Code:

Sub Create_Dynamic_Table2()
    Dim tbObj As ListObject
    Dim TblRng As Range
    With Sheets("Example5")
        .Range("A1").Select
        Selection.CurrentRegion.Select
        Set tbObj = .ListObjects.Add(xlSrcRange, Selection, , xlYes)
        tbObj.Name = "DynamicTable2"
        tbObj.TableStyle = "TableStyleMedium15"
    End With
End Sub

• Press F5 to run the code.

Example 6 – Use Excel VBA to Create a Dynamic Table from Range

STEPS:

• Open the ribbon and select the Developer tab.
• Click on Visual Basic to open the Visual Basic Editor or press Alt + F11. You can also right-click on the sheet and select View Code.
• Go to Insert and select Module. The visual basic window will open.
• Copy and paste the following VBA code.

VBA Code:

Sub Create_Dynamic_Table3()
    Dim tableObj As ListObject
    Dim TblRng As Range
    With Sheets("Example6")
        lLastRow = .UsedRange.Rows.Count
        lLastColumn = .UsedRange.Columns.Count
        Set TblRng = .Range("A1", .Cells(lLastRow, lLastColumn))
        Set tableObj = .ListObjects.Add(xlSrcRange, TblRng, , xlYes)
        tableObj.Name = "DynamicTable3"
        tableObj.TableStyle = "TableStyleMedium16"
    End With
End Sub

• Press F5 to run the code.

Download Practice Workbook

Related Articles

• How to Compare Two Tables for Differences with Excel VBA
• Excel VBA Code for Each Row in a Table
• Reference Table Column by Name with VBA in Excel
• How to Use Table Reference with Excel VBA
• Excel VBA: Insert Data into Table