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

 

[sspatriots]

I think I have figured it out. Posting the code I came up with for two different tables and it seems to be working so far.

Sub ResizeTables()

Dim rng1 As Range
Dim rng2 As Range

Dim tb1 As ListObject
Dim tb2 As ListObject

Dim tbName1 As String
Dim tbName2 As String

tbName1 = Range("A8")
    
Set rng1 = Range(tbName1)
Set tb1 = ActiveSheet.ListObjects(tbName1)
    
    rng1.ClearContents
    
    rng1.Cells(1, 1).Select
    ActiveCell.FormulaR1C1 = _
        "='H:\Jobs\00 ENGINEERING DATA\[Job List.xlsm]Jobs'!R[1]C2"
    
    rng1.Cells(1, 2).Select
    ActiveCell.FormulaR1C1 = _
        "=IF('H:\Jobs\00 ENGINEERING DATA\[Job List.xlsm]Jobs'!R[1]C25="""","""",'H:\Jobs\00 ENGINEERING DATA\[Job List.xlsm]Jobs'!R[1]C25)"
    
    tb1.Resize tb1.Range.Resize(Range("B8").Value)
        
        
        
tbName2 = Range("A9")
    
Set rng2 = Range(tbName2)
Set tb2 = ActiveSheet.ListObjects(tbName2)
    
    rng2.ClearContents
    
    rng2.Cells(1, 1).Select
    ActiveCell.FormulaR1C1 = _
        "='H:\Jobs\00 PO ARCHIVE\2019\[2019_PO_Folder_Contents.xlsm]Sheet1'!R[1]C1"
        
    tb2.Resize tb2.Range.Resize(Range("B9").Value)
        
        
        
        

End Sub

 

[Peter_SSs]

Seems from post 2 that your question in post 1 provided insufficient information for anybody to help with what you actually wanted (I've mentioned that before )
For example, there was no mention in post 1 about

• clearing the table data
• inserting formulas in the tables

To clear and resize the tables, as far as I can tell, all you would need is this.
I have assumed that A2:A14 all have valid table names of tables that exist on sheet "Drops" and column B contains valid numbers for the rows required.

Sub Clear_And_Resize_Tables()
  Dim c As Range
  
  For Each c In Range("A2:A14")
    With Sheets("Drops").ListObjects(c.Value)
      .DataBodyRange.ClearContents
      .Resize (.Range.Resize(c.Offset(, 1).Value))
    End With
  Next c
End Sub

As far as adding formulas, there would need to be some way to know what the formulas were for each table and which columns the formulas have to go in.

 

[sspatriots]

At the time I made the post all I was trying to find out was exactly what I was asking for. I believed that once I could get past that hurdle I could manipulate it to whatever I needed to do. I wasn’t even sure at that time what else I was going to do with it. I just posted the final outcome in case anyone else searching the internet could use it. When I return to work tomorrow I will however go back and take a closer look at what you responded with and try and incorporate it into my code to see if it makes it faster. I’m all for optimization. Thank you for your response.

 

[sspatriots]

Seems from post 2 that your question in post 1 provided insufficient information for anybody to help with what you actually wanted (I've mentioned that before )
For example, there was no mention in post 1 about

• clearing the table data
• inserting formulas in the tables

To clear and resize the tables, as far as I can tell, all you would need is this.
I have assumed that A2:A14 all have valid table names of tables that exist on sheet "Drops" and column B contains valid numbers for the rows required.

Sub Clear_And_Resize_Tables()
  Dim c As Range
 
  For Each c In Range("A2:A14")
    With Sheets("Drops").ListObjects(c.Value)
      .DataBodyRange.ClearContents
      .Resize (.Range.Resize(c.Offset(, 1).Value))
    End With
  Next c
End Sub

As far as adding formulas, there would need to be some way to know what the formulas were for each table and which columns the formulas have to go in.

Hi,

I did try the code above by itself; however, it stops at the line below when I select ":

.DataBodyRange.ClearContents

The "Run-time error '91': Object variable or With block variable not set" pops up. I'm not sure how to stop that from happening.

 

[sspatriots]

Actually, I see what I did to cause it get that error. I ran the code twice and the second time the error occurred.

 

[Peter_SSs]

I ran the code twice and the second time the error occurred.

Generally I could run the code as many times as I liked without error. However, I did manage to reproduce the error in some circumstances (but not all) when the value in column B for a table was 2.

See if this overcomes the problem.

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 With
  Next c
End Sub

 

[sspatriots]

That works perfectly. I think I had to add an "End If" in there just before the "End With" to get it to run completely through, but it does work great. Thanks again. SS

 

[Peter_SSs]

I think I had to add an "End If" in there just before the "End With" to get it to run completely through

Unless you changed something else in my code or its layout, doing that would cause a 'Compile error' and the code would not run as you see below where I have added such a line.

If you need to discuss changes further, please copy/paste the actual code you ran.

 

[sspatriots]

Sorry, I see what I did. I put the line that says:

.DataBodyRange.ClearContents

down one line to be just over top of the line that says:

.Resize (.Range.Resize(c.Offset(, 1).Value))

I didn't realize that by moving it down to align with the ".Resize" line it would cause an error. If you don't mind, could you explain to me why that shouldn't still work?


Thanks, SS