VBA Create Dynamic Table in Excel

VBA Create Dynamic Table in Excel. Let us see different examples and step by step instructions. In this tutorial we use ListObjects collection to create table. And also use ListObject object. Which belongs to ListObjects collection. We have different examples with with different table names with different styles. You can also change table style, name of the table and sheet name.

First Example to Create Dynamic Table in Excel VBA

Let us see the example to Create Table dynamically in Excel VBA

'Create Dynamic Table in Excel VBA
Sub VBAF1_Create_Dynamic_Table()
    
    'Variable Declaration
    Dim tableListObj As ListObject
    Dim TblRng As Range
    
    'Sheet Name
    With Sheets("Table")
            
        'Find Last Row
        lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        'Find Last Column
        lLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
        'Range to create table
        Set TblRng = .Range("A1", .Cells(lLastRow, lLastColumn))
        
        'Create table in above specified range
        Set tableListObj = .ListObjects.Add(xlSrcRange, TblRng, , xlYes)
        
        'Specifying table name
        tableListObj.Name = "FirstDynamicTable"
        
        'Specify table style 
        tableListObj.TableStyle = "TableStyleMedium14"
    End With
    
    'Display message on the screen
    MsgBox "Table has created successfully.", vbInformation, "VBAF1"
 
 End Sub

Output: Here is the following output screenshot for the above macro. The output message displays on the system screen.

Second Example for Dynamic Table creation in Excel VBA

Here is another example for dynamic table creation in Excel VBA.

 Sub VBAF1_Create_Dynamic_Table1()
    
    'Variable Declaration
    Dim tableListObj As ListObject
    Dim TblRng As Range
    
    'Sheet Name
    With Sheets("Table")
            
        'Select cell - You can change as per your requirement
        .Range("A1").Select
        
        'Selects the current region
        Selection.CurrentRegion.Select
                
        'Create table in above specified range
        Set tableListObj = .ListObjects.Add(xlSrcRange, Selection, , xlYes)
        
        'Specifying table name
        tableListObj.Name = "SecondDynamicTable1"
        
        'Specify table style
        tableListObj.TableStyle = "TableStyleMedium15"
    End With
    
    'Display message on the screen
    MsgBox "Table has created successfully.", vbInformation, "VBAF1"
 
 End Sub

Output: Here is the following output screenshot for the above macro. The output message displays on the system screen.

Third Example to Create Table Dynamically in Excel VBA

Let us see one more example to create table dynamically in Excel VBA.

Sub VBAF1_Create_Dynamic_Table2()
    
    'Variable Declaration
    Dim tableListObj As ListObject
    Dim TblRng As Range
    
    'Sheet Name
    With Sheets("Table")
            
        'Find Last Row
        lLastRow = .UsedRange.Rows.Count
        
        'Find Last Column
        lLastColumn = .UsedRange.Columns.Count
        
        'Dynamic Range to create table
        Set TblRng = .Range("A1", .Cells(lLastRow, lLastColumn))
        
        'Create table in above specified range
        Set tableListObj = .ListObjects.Add(xlSrcRange, TblRng, , xlYes)
        
        'Specifying table name
        tableListObj.Name = "ThirdDynamicTable"
        
        'Specify table style
        tableListObj.TableStyle = "TableStyleMedium16"
    End With
    
    'Display message on the screen
    MsgBox "Table has created successfully.", vbInformation, "VBAF1"
 
 End Sub

Output: Here is the following output screenshot for the above macro. The output message displays on the system screen.

Note: Before running above macro, create sheet called ‘Table’, otherwise change sheet name in the above specified macro.

Table Name: You can find the table name by following the below specified instructions.

1. You can select the table range.
2. Go to Table Design from the Excel ribbon.
3. Go to Properties group.
4. You can see the selected name of the table under Table Name in the textbox.
5. You can also edit table name manually in the specified box and press enter.

Instructions to Use Example Macro Code

You can follow step by step instructions to run above macro example VBA code.

• Open Visual Basic Editor(VBE) by clicking Alt +F11
• Go to code window by clicking F7
• Copy above specified macro or procedure
• Paste above copied code in code window in the visual basic editor(VBE)
• Run macro by clicking F5 or Run command

Other Related References

Click on the following links for related reference articles. It helps for your reference.

VBA Tables Tables Examples VBA Functions List