Resize Tables using Table Names from Col A and the new Row count from Corresponding Col B

[sspatriots]

Hi,

I'm trying to use a macro I found here in this group, but with a slight modification. I have multiple tables on the same worksheet called "Drops" that I want to resize with the same macro. Column A2:A14 consist of the worksheets table names and column B2:B14 consist of the corresponding number of rows that I want to resize all of these tables to. I've added a variable to capture the table name from column A, using the variable named "tbName1". However, I'm having trouble replacing "Table1" in the code with "tbName1". Any assistance would be greatly appreciated.


Thanks, SS

Sub ResizeTables()

Dim rng1 As Range
Dim tb1 As ListObject
Dim tbName1 As String

    tbName1 = Range("A8")
        
    Set rng1 = Range("Table1[#All]").Resize(Range("B8").Value)
        
    Set tb1 = ActiveSheet.ListObjects("Table1")
    tb1.Resize rng1

End Sub

 

[Peter_SSs]

could you explain to me why that shouldn't still work?

If you do that the code will "work" in that it will not error. However, it would not resize a table under some circumstances.
If you want to move that bit of code it would be safest to put the End If up a little further like this

Sub Clear_And_Resize_Tables_v2()
  Dim c As Range
  
  For Each c In Range("A2:A14")
    With Sheets("Drops").ListObjects(c.Value)
      If WorksheetFunction.CountA(.Range) > .Range.Columns.Count Then
        .DataBodyRange.ClearContents
      End If
      .Resize (.Range.Resize(c.Offset(, 1).Value))
    End With
  Next c
End Sub

 

[sspatriots]

If you do that the code will "work" in that it will not error. However, it would not resize a table under some circumstances.
If you want to move that bit of code it would be safest to put the End If up a little further like this

Sub Clear_And_Resize_Tables_v2()
  Dim c As Range
 
  For Each c In Range("A2:A14")
    With Sheets("Drops").ListObjects(c.Value)
      If WorksheetFunction.CountA(.Range) > .Range.Columns.Count Then
        .DataBodyRange.ClearContents
      End If
      .Resize (.Range.Resize(c.Offset(, 1).Value))
    End With
  Next c
End Sub

Thanks. Not sure I understand it 100%, but will go back try this out as well.

 

[Peter_SSs]

Not sure I understand it 100%,

Well, let's suppose that you had the code as you originally changed it - to this I believe

Sub Clear_And_Resize_Tables_v2()
  Dim c As Range
  
  For Each c In Range("A2:A14")
    With Sheets("Drops").ListObjects(c.Value)
      If WorksheetFunction.CountA(.Range) > .Range.Columns.Count Then
        .DataBodyRange.ClearContents
        .Resize (.Range.Resize(c.Offset(, 1).Value))
      End If
    End With
  Next c
End Sub

If you had data in one of the tables and column B said to resize that table to 5 rows and ran the code it would clear the data from the table and resize it to 5 rows as desired.
However, if you then decided that you actually wanted that table say 7 rows so changed the relevant column B value to 7 and ran the code again, then that table would not be resized from 5 rows to 7 rows.

With the code in post #11 though, the table would be resized from 5 rows to 7.

 

[sspatriots]

Well, let's suppose that you had the code as you originally changed it - to this I believe

Sub Clear_And_Resize_Tables_v2()
  Dim c As Range
 
  For Each c In Range("A2:A14")
    With Sheets("Drops").ListObjects(c.Value)
      If WorksheetFunction.CountA(.Range) > .Range.Columns.Count Then
        .DataBodyRange.ClearContents
        .Resize (.Range.Resize(c.Offset(, 1).Value))
      End If
    End With
  Next c
End Sub

If you had data in one of the tables and column B said to resize that table to 5 rows and ran the code it would clear the data from the table and resize it to 5 rows as desired.
However, if you then decided that you actually wanted that table say 7 rows so changed the relevant column B value to 7 and ran the code again, then that table would not be resized from 5 rows to 7 rows.

With the code in post #11 though, the table would be resized from 5 rows to 7.

Thanks. Makes more sense now.