Excel VBA to Create Table from Range (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

6 Examples of Excel VBA to Create Table from Range

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.

expression .Add(SourceType, Source, LinkSource, HasHeaders,Destination)

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.

Excel VBA to Generate Table from Range

  • 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.

Excel VBA to Generate Table from Range

  • 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.

VBA Code Explanation

Sub Create_Table()

Sub is a part of code that is used to handle the work in the code but will not return any value. It is also known as subprocedure. So we name our procedure Create_Table().

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

This is the main code line with which the range is converted as a table. As we already know that ListObjects.Add to turn a range into an Excel table. And we use xlSrcRange as a source type. Also, we declare our range  Range(“B4:D9”). And finally, name our table as Table1.

End Sub

This will end the procedure.

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.

VBA Code Explanation

Dim tb2 As Range
Dim wsht As Worksheet

The DIM statement in VBA refers to “declare,” and it must be used to declare a variable. So, we declare our range to tb2 and worksheet to ws.

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

VBA Set simply allows us to avoid having to type in the range we need to pick over and over again when running the code. So, we set our range to the current region and our worksheet to the active worksheet.

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

With this line of code, we create the table from the range and name our table 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


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.

Apply VBA to Create Dynamic Table from Range

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

VBA Code Explanation

Sub Create_Dynamic_Table1()

This line indicates the name of the subprocedure.

Dim tbOb As ListObject
Dim TblRng As Range

This two-line is used for variable declaration.

With Sheets("Example4")

The With statement allows you to make a sequence of statements on a single object without having to requalify the object’s name. So, we enclose the With statement with the sheet name.

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

Those are for respectively to find the last row and last column.

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

Range to create the table.

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

Create a table in the above-specified range.

tbOb.Name = "DynamicTable1"

Specifying table name

tbOb.TableStyle = "TableStyleMedium14"

Specify table style.

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

Sabrina Ayon
Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo