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.

Download Practice Workbook

You can download the workbook and practice with them.

6 Examples of Excel VBA to Create Table from Range

Tables started off as lists in Excel’s menu edition, but those grew in functionality in the ribbon variants. Converting a data range to a table expands capability, allowing you to work more quickly and easily. To convert the range into a table using VBA is the easiest way than using the ribbon.

Suppose, we have a simple dataset that contains some items in column B, those items’ quantity in column C, and total sales for each item in column D. Now, we want to convert the data range into a table. Let’s demonstrate different examples and step-by-step instructions to create a table from range B4:D9 with Excel VBA.

Use ListObjects.Add to turn a range into an Excel table. The Spreadsheet object has a characteristic ListObjects. ListObjects has a technique called Add. The criteria for .Add are as follows.

And, use the SourceType xlSrcRange.

1. Excel VBA to Generate Table from Range

With Excel VBA, users can easily use the code which acts as excel menus from the ribbon. To use the VBA code to generate a table from the range, let’s follow the procedure down.

STEPS:

• Firstly, go to the Developer tab from the ribbon.
• Secondly, from the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.
• Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.

• This will appear in the Visual Basic Editor where we write our codes to create a table from range.
• Thirdly, click on Module from the Insert drop-down menu bar.

• This will create a Module in your workbook.
• And, copy and paste the VBA code shown below.

VBA Code:

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

• After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

You don’t need to change the code. All you can do is just change the range as per your requirements.

• And, finally, following the steps will create a table from range B4:D9.

Sub Create_Table()

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

End Sub

Read More: How to Update Pivot Table Range (5 Suitable Methods)

2. Construct Table from Range Using Excel VBA

Let’s see another example to construct a table from range using Excel VBA.

STEPS:

• First, go to the Developer tab from the ribbon.
• Second, click on Visual Basic to open the Visual Basic Editor.
• Another way to open the Visual Basic Editor is simply to press Alt + F11.
• Or, right-click on the sheet, then select View Code.
• Next, go to Insert and select Module from the drop-down menu.
• And, this will open up the visual basic window.
• After that, 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

• Further, press the F5 key or click on the Run Sub button to run the code.

• And, you will get the result as shown in Method 1.

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"

Read More: How to Use an Excel Table with VBA (9 Possible Ways)

3. Create Table from Range with VBA in Excel

Let’s look at another example of utilizing Excel VBA to create a table from a range.

STEPS:

• To begin, select the whole range that you want to convert into a table.
• Second, click the Developer tab on the ribbon.
• Third, launch the Visual Basic Editor by clicking on Visual Basic.
• Alternatively, you may access the Visual Basic Editor by pressing Alt + F11.
• Or, right-click on the sheet and choose View Code from the menu.
• Next, pick the Module from the drop-down box under Insert.
• And the visual basic window will appear.
• Write the code there.

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

• Finally, press the F5 key to run the code.

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

Read More: How to Make a Table in Excel (With Customization)

Similar Readings

• Calculated Field Sum Divided by Count in Pivot Table
• How to Illustrate Relative Frequency Distribution in Excel
• Excel Pivot Table Group by Week (3 Suitable Examples)
• [Fix] Cannot Group Dates in Pivot Table: 4 Possible Solutions
• How to Make an Amortization Table in Excel (4 Methods)

4. Apply VBA to Create Dynamic Table from Range

Let’s have a glance at another way to generate a table from a range using Excel VBA.

STEPS:

• To start, open the ribbon and select the Developer option.
• Then, to access the Visual Basic Editor, click on Visual Basic.
• Pressing Alt + F11 will also bring up the Visual Basic Editor.
• Alternatively, right-click the sheet and choose View Code from the menu that appears.
• Now, from the Insert drop-down option, pick Module.
• Then copy and paste the VBA code that follows.

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

• Run the code by pressing the F5 key.

• As illustrated in Method 1‘s illustration, the table will be built from the range.

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"

Read More: Create Table in Excel Using Shortcut (8 Methods)

5. Make Dynamic Table from Range

Now, take a look at another Excel VBA method for creating a table from a range.

STEPS:

• To begin, open the ribbon and choose Developer from the drop-down menu.
• Then select Visual Basic to open the Visual Basic Editor.
• The Visual Basic Editor may also be accessed by pressing Alt + F11.
• Alternatively, you may right-click the sheet and select View Code from the pop-up menu.
• After that, select Module from the Insert drop-down menu.
• Then 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

• Finally, run the code by pressing F5 on your keyboard and you see the result in your worksheet.

• And, as demonstrated in Method 1‘s illustration, the table will be constructed from the range.

Read More: How to Make Excel Tables Look Good (8 Effective Tips)

6. Use Excel VBA to Build Dynamic Table

Let’s explore another Excel VBA way of constructing a table from a range.

STEPS:

• In the beginning, go to the Developer tab > Visual Basic > Insert > Module.
• Or, right-clicking on the worksheet will open up a window. From there go to the View Code.
• And, this will take you to the Visual Basic Editor field, where we can write VBA Macros.
• On the other hand, pressing Alt + F11 will also open the Visual Basic Editor.
• After that, type the 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

• And, run the code to see the result by pressing the F5 key.

• And, the table will be created from the range as shown in the picture of Method 1.

Read More: How to Create a Table in Excel with Data (5 Ways)

Conclusion

The above methods will assist you to create a table from range in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!

Related Articles

• How to Lookup a Table and Return Values in Excel (3 Simple Ways)
• [Fix] The Pivot Table Name Is Not Valid (7 Causes with Solutions)
• How to Get a Count in Excel Pivot Table Calculated Field
• [Fixed!] Pivot Table Field Name Already Exists (2 Quick Methods)
• Pivot Table is Not Picking up Data in Excel (5 Reasons)
• How to Edit a Pivot Table in Excel (5 Methods)