title: Resize each table on a worksheet with a variable number of rows
body: <p>I’m new to VBA, I’m struggling with a piece of code, and I’m hoping someone can help. I have a series of 78 tables, each with 1 column, in Sheet 1 of Workbook 1 (ThisWorkbook), named Table A through Table BZ, respectively. Each table is the source for a corresponding named range, “_TableA” through “_TableBZ” that I’m using to drive a series of dependent drop-downs on Sheet 2.</p>
  
  <p>I have figured out the code to copy and paste values from Workbook 2 into each of the 78 tables in Workbook 1, one table at a time, effectively refreshing the available choices in the dependent drop-downs. If the number of values pasted from Workbook 2 is greater than the size of the table in Workbook 1, the table in Workbook 1 is automatically resized to fit the updated dataset. If the number of values pasted from Workbook 2 is less than the size of the table in Workbook 1, the table needs to be resized so that blank values don’t show up in the dependent drop-downs.</p>
  
  <p>I started by trying to include the resize the table within the copy / paste loop but couldn’t seem to get the variables defined properly and struggled with choosing between the Range.Resize and ListObjects.Resize methods. I’ve now tried to create a second loop to go through all the tables and resize one at a time after all the copying / pasting is done. Is there any difference in the two approaches?</p>
  
  <p>I can resize Table A with the following code, but it doesn’t progress to Table B in the next loop (i = 2).</p>
  
  <pre><code>Dim CurrentTable As ListObject
  i = 1
  Do
      ThisWorkbook.Activate
      Set CurrentTable = ActiveSheet.ListObjects(1)
      CurrentTable.Resize Range("N1:N10")
      i = i + 1
  Loop Until i = 78
  </code></pre>
  
  <p>I also need to resize each table to an appropriate number of rows, so I tried including another range variable. The following code does not resize Table A and gives me an Application-defined or object-defined error on “CurrentTable.Resize Range(TableRange)”:</p>
  
  <pre><code>Dim TableRange As Range
  Dim CurrentTable As ListObject
  i = 1
  Do
      ThisWorkbook.Activate
      Set CurrentTable = ActiveSheet.ListObjects(1)
      Set TableRange = Range(CurrentTable).Resize (CurrentTable.Range.Rows.Count, 1)
      CurrentTable.Resize Range(TableRange)
      i = i + 1
  Loop Until i = 78
  </code></pre>
  
  <p>I thought that the Rows.Count in the Resize range might be messing it up, but Resize(CurrentTable.Range.Rows,1) gives me the same error.</p>
  
  <p>So I need some combination that will resize the CurrentTable to the number of rows that contain data and then move to the next table in the series.</p>
  
  <p>Any suggestions would be much appreciated!</p>