title: Excel VBA: How to find first empty row within a Table for a Loop routine?
body: <p>I reformatted a range of <code>Sheets("Records")</code> in a workbook as a <code>Table (named "RecordsTable")</code> to make it easier to do <code>INDEX(MATCH,MATCH)</code> functions for generating reports.... but now I screwed up my looping routine for filling that range from the input on <code>Sheets("FORM")</code>.</p>
  
  <p>It used to be:</p>
  
  <pre><code>Set r = Sheets("Records").Range(A &amp; Rows.Count).End(x1Up).Offset(1, 0)
  
  i = 0
  
     For Each c In Range("dataRange")              
     'dataRange is a list of cells to reference from the FORM input sheet
  
     r.Offset(0, i).Value = Worksheets("FORM").Range(c)
     i = i + 1
  Next
  </code></pre>
  
  <p>However this code is now selecting the first row at the END of <code>"RecordsTable"</code> (row 501, as I defined 500 rows in my table) and inserting the data there.</p>
  
  <p>I tried to change it to this:</p>
  
  <pre><code>Set r = Sheets("Records").ListObjects("RecordsTable").DataBodyRange("A" &amp; Rows.Count).End(x1Up).Offset(1, 0)
  
  i = 0
  
     For Each c In Range("dataRange")              
  
     r.Offset(0, i).Value = Worksheets("FORM").Range(c)
     i = i + 1
  Next
  </code></pre>
  
  <p>But this code is still selecting row 501 and making that row part of <code>"RecordsTable"</code>.
  How can I properly <code>Set "r" to =</code> the first empty row in <code>"RecordsTable"</code>?</p>
  
  <p>For reference, <code>Column "A" in "RecordsTable"</code> has the header <code>[INV #]</code>. Also, when I step into the <code>"Set r = ..."</code> line, <code>Rows.Count</code> is returning a value of 1million+ (ie, total rows on the sheet) - if I understand this correctly, I want it to return a value of 500 (ie, total rows in table) - is that correct?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p><code>"dataRange"</code> is a single column list of cell references (I do have them labeled in column B, as @chrisneilsen suggest:</p>
  
  <p><strong>A</strong></p>
  
  <p>J6</p>
  
  <p>Y6</p>
  
  <p>J8</p>
  
  <p>J10</p>
  
  <p>Y8</p>
  
  <p>etc.</p>
  
  <p>They are the cells on <code>Sheets("FORM")</code> that I need to pull data from and populate into my table, in the order indicated in <code>"dataRange"</code>.</p>