Resize table using VBA

Question

Hello,

I currently use the following code in order to copy data to another worksheet.

However, when I eliminate rows on the original worksheet, the range of the table for the second worksheet remains the same when I run the macro.

I was wondering if someone could help me understand which function or code I could use so that the table size on the second worksheet is updated to the size of the range whenever I run the macro the way it is.

Thanks :)

Answer 1

To update the table size on the second worksheet to match the size of the range on the original worksheet, you can use the ListObject.Resize method in VBA. This method resizes the table to match the size of the range specified. Here is an example of how you can modify your code to include this method:

Sub CopyData()
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim targetTable As ListObject
    
    ' Set the source range to copy from
    Set sourceRange = Worksheets("Sheet1").Range("A1:C10")
    
    ' Set the target range to copy to
    Set targetRange = Worksheets("Sheet2").Range("A1")
    
    ' Copy the data from the source range to the target range
    sourceRange.Copy Destination:=targetRange
    
    ' Resize the table on the second worksheet to match the size of the range
    Set targetTable = Worksheets("Sheet2").ListObjects("Table1")
    targetTable.Resize targetRange.Resize(sourceRange.Rows.Count, sourceRange.Columns.Count)
End Sub

In this example, the ListObject.Resize method is used to resize the table named "Table1" on the second worksheet to match the size of the range copied from the first worksheet. The targetRange.Resize method is used to create a range of the same size as the source range, which is then passed to the ListObject.Resize method.

---

References:

• How to delete every other row on an Excel worksheet
• Methods for transferring data to Excel from Visual Basic