VBA Resize Table in Excel

VBA Resize Table in Excel. This means you can add rows or columns to table or delete rows or columns from table and creates new table range. To do this we use Resize method of the ListObject. The ListObject object is belongs to ListObjects collection. In this tutorial we have explained multiple examples with explanation. We also shown example output screenshots. We have specified multiple examples in the following tutorial. You can change table and sheet name as per your requirement. We also specified step by step instructions how to run VBA macro code at the end of the session.

Syntax of the Resize Method using VBA in Excel

Here is the syntax of the Resize method from Table on the worksheet using VBA in Excel.

ListObject.Resize(Range)

Where Range is a mandatory argument. It contains the range data type. Represents the table new range.

Example to Resize Table by specifying static range

Let us see the example to Resize table by specifying static range on the Worksheet. The sheet name defined as ‘Table‘. And we use table name as ‘MyTable1‘. You can change these two as per your requirement.
Let us use Resize method of the ListObject object to resize table.

'Resize Table by specifying static range using VBA
Sub VBAF1_Resize_Table_Static_Range()
    
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    
    'Define Variable
    sTableName = "MyTable1"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
        
    'Resize the table
    loTable.Resize Range("A1:D15")
    
End Sub

Output: Here is the following output screenshot of above example macro VBA code.

Add Rows & Columns to Table using Resize method

Let us see another example to add rows & columns to table using Resize method on the Worksheet. We find number of existing rows and columns of the table. In the below procedure you can specify to a variable how many rows or columns to add to table.

'VBA Add 5 rows & 5 columns to table
Sub VBAF1_Resize_Table_Add_Rows_Columns()
    
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    Dim loRows As Integer, loColumns As Integer
    Dim iNewRows As Integer, iNewColumns As Integer
    
    'Define Variable
    sTableName = "MyTable1"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
    'Find number of rows & columns in the table
    loRows = loTable.Range.Rows.Count
    loColumns = loTable.Range.Columns.Count
    
    'Specify Number of Rows & Columns to add to table
    iNewRows = 5: iNewColumns = 5
            
    'Resize the table
    loTable.Resize loTable.Range.Resize(loRows + iNewRows, loColumns + iNewColumns)
    
End Sub

Output: Let us see the following output screenshot of above example macro VBA code.

Note: You can also add either rows or columns to table.

 'To Add Only Rows
 loTable.Resize loTable.Range.Resize(loRows + iNewRows, )
 
 'To Add Only Columns
 loTable.Resize loTable.Range.Resize(, loColumns + iNewColumns)

Delete Rows & Columns to Table using Resize method

Let us see another example to delete rows & columns from table using Resize method on the Worksheet. We find number of existing rows and columns of the table. In the below procedure you can specify to a variable how many rows or columns to delete to table.

'VBA Delete 5 rows & 5 columns from table
Sub VBAF1_Resize_Table1_Delete_Rows_Columns()
    
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    Dim loRows As Integer, loColumns As Integer
    Dim iRows As Integer, iColumns As Integer
    
    'Define Variable
    sTableName = "MyTable1"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
    'Find number of rows & columns in the table
    loRows = loTable.Range.Rows.Count
    loColumns = loTable.Range.Columns.Count
    
    'Specify Number of Rows & Columns number to delete from table
    iRows = 5: iColumns = 5
            
    'Resize the table
    loTable.Resize loTable.Range.Resize(loRows - iRows, loColumns - iColumns)
    
End Sub

Output: Let us see the following output screenshot of above example macro VBA procedure.

Note: You can also delete either rows or columns from table.

 'To Delete Only Rows
 loTable.Resize loTable.Range.Resize(loRows - iRows, )
 
 'To Delete Only Columns
 loTable.Resize loTable.Range.Resize(, loColumns - iColumns)

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
• The difference between before and after macro, see the above output screenshot.

Other Related References

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

VBA Tables Tables Examples