Methods for finding first available empty row in table in not working

Question

I normally hesitate to use tables but this time is for many work related reasons and I must use tables. Therefore now I am in the process of converting the range codes to table modified codes and Im having lots of problems which I figure out day by day. This one but is annoying me. This is a code to find the last full row of a table column and insert the data from my userform textboxes to the next row which is empty to take in data.

Private Sub but_spl2addweld_Click()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("PL2_steel")
    
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Sheets("PL2_steel").ListObjects("Tbl_spl2weldings")

    Dim y As Long
    Dim Ir As Long

    Ir = sh.ListObjects("Tbl_spl2weldings").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 

       With tbl
        If Me.Combo_SPL2weldoptions.Value = "Option 1_ Base material-Filler material" Then
         .DataBodyRange(Ir + 1, 1).Value = "pl2.St." & Me.txt_SPL2code.Value
         .DataBodyRange(Ir + 1, 2).Value = Me.Txt_spl2weldid.Value
         .DataBodyRange(Ir + 1, 3).Value = Me.Combo_SPL2weldoptions.Value

        End If
       End With
       
 End Sub

I tried many different methods to find the last full row of column(1) 'Ir' and put my new data in the next row which is empty 'Ir+1' like in the range system. But nothing works as I want. For example the current code, puts the new data always in the same row which is the second row after the header, regardless of column 1 being full or empty.

Answer 1

You want a way to get the first row that is empty in a table's column. To do this:

• Iterate through each row
• Check a specific column to see if there is data
• If there is data, continue searching
• If there is not data, we have found the first empty row

Here's a function that does this:

Private Function FirstEmptyColumnRow(table As ListObject, columnNumber As Long) As Long

    ' Purpose:
    ' Return the row number of the first empty cell in the table's column
    
    ' Store the table data as an array
    Dim tableData() As Variant
    tableData = table.DataBodyRange.Value
    
    ' Iterate through the rows
    Dim Row As Long
    For Row = 1 To UBound(tableData, 1)
    
        ' Check if the data in the column of this row is Empty
        If IsEmpty(tableData(Row, columnNumber)) Then
        
            ' If the data is Empty, return the Row number and exit the function
            FirstEmptyColumnRow = Row
            Exit Function
            
        End If
    
    Next Row
    
    ' If there were no empty rows, then set the value to the number of rows
    FirstEmptyColumnRow = Row

End Function

Since this is written as a function, you can use it for whatever tables and column numbers you want. In your case, something like this:

Dim lr as Long
lr = FirstEmptyColumnRow(tbl, 1)

Whenever possible, try to keep your code doing one simple job. See, this code only finds the row that is empty. It doesn't add a new row, or insert new data. Now, I would create a sub to take this row number, and add the data in the way that you want.

Finally, I highly recommend learning how to store tables as arrays, because they are much faster to work with in VBA than working with the tables directly.