vbaexcelloops

Excel VBA: How to find first empty row within a Table for a Loop routine?


I reformatted a range of Sheets("Records") in a workbook as a Table (named "RecordsTable") to make it easier to do INDEX(MATCH,MATCH) functions for generating reports.... but now I screwed up my looping routine for filling that range from the input on Sheets("FORM").

It used to be:

Set r = Sheets("Records").Range(A & 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

However this code is now selecting the first row at the END of "RecordsTable" (row 501, as I defined 500 rows in my table) and inserting the data there.

I tried to change it to this:

Set r = Sheets("Records").ListObjects("RecordsTable").DataBodyRange("A" & 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

But this code is still selecting row 501 and making that row part of "RecordsTable". How can I properly Set "r" to = the first empty row in "RecordsTable"?

For reference, Column "A" in "RecordsTable" has the header [INV #]. Also, when I step into the "Set r = ..." line, Rows.Count 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?

EDIT

"dataRange" is a single column list of cell references (I do have them labeled in column B, as @chrisneilsen suggest:

A

J6

Y6

J8

J10

Y8

etc.

They are the cells on Sheets("FORM") that I need to pull data from and populate into my table, in the order indicated in "dataRange".


Solution

  • Assuming you really have a Table, adding data to a Table (ListObject) using it's properties and methods:

    Sub Demo()
        Dim lo As ListObject
        Dim c As Range
    
        Set lo = Worksheets("Records").ListObjects("RecordsTable")
    
        For Each c In Sheets("V").Range("dataRange")
            If Not lo.InsertRowRange Is Nothing Then
                lo.InsertRowRange.Cells(1, 1) = Sheets("FORM").Range(c)
            Else
                lo.ListRows.Add.Range.Cells(1, 1) = Sheets("FORM").Range(c)
            End If
        Next
    End Sub
    

    Note: looping a range on sheet V and using that as a pointer to data on sheet FORM, copied from your answer - I'm assuming you know what you are doing here

    Based on OP comment, adding data a single new row

    Sub Demo()
        Dim lo As ListObject
        Dim c As Range, TableRange As Range
        Dim i As Long
    
        Set lo = Worksheetsheets("Records").ListObjects("RecordsTable")
    
        If Not lo.InsertRowRange Is Nothing Then
            Set TableRange = lo.InsertRowRange
        Else
            Set TableRange = lo.ListRows.Add.Range
        End If
        i = 1
        For Each c In Sheets("V").Range("dataRange")
            TableRange.Cells(1, i) = Sheets("FORM").Range(c)
            i = i + 1
        Next
    End Sub
    

    Note, this assumes that the order of the table columns is the same as the order of dataRange. It may be better to include table field names in dataRange to avoid any mismatch issues

    As mentioned in updated OP, if column labels are in the next column, replace the For loop with this (and add Dim r as Range, col as long to declarations)

        For Each c In Sheets("V").Range("dataRange")
            If Not c = vbNullString Then
                Set r = Worksheets("FORM").Range(c.Value)
                col = lo.ListColumns(c.Offset(, 1).Value).Index
                TableRange.Cells(1, col) = r.Value
            End If
        Next