Look at first empty row after the first few rows of my excel are empty but the rest are populated

Question

Now, my Excel macro is concatenating the unit and number columns. The numbers are retrieved based on an ID, and occasionally the number I want to combine the unit with may not be available for that particular ID. The number I want to concatenate with the unit only starts on row 6, occasionally on row 8, but it will at least start on row 2. I am using find last row, but that does not always work. The title appears on row 1.

Without giving it a range to look up to, say, row 100 because I occasionally have more than 100 rows, I want it to disregard the empty rows. The code below is what I currently have and works if the column is fully populated until the end.

rowEmpty = 2
    Do While IsEmpty(ws_Export.cells(rowEmpty, 9)) = False
        rowEmpty = rowEmpty + 1
    Loop
    'rowEmpty is now set as the first empty row (sets the range of the table)
    
    'Add units within the same cell as the shunt
    For s = 2 To rowEmpty - 1
        cells(s, 9) = cells(s, 9) & " " & cells(s, 8)
Next s

Answer 1

Concatenate If Not Blank

• Replace the J with I to replace as required.

Option Explicit

Sub AppendUnits()

    Const WS_NAME As String = "Export"
    Const CAL_FIRST_CELL As String = "I2"
    Const UNIT_COLUMN As String = "H"
    Const DST_COLUMN As String = "J" ' Result
    Const DELIMITER As String = " "
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet: Set ws = wb.Sheets(WS_NAME)
    
    Dim crg As Range, rCount As Long
    
    With ws.Range(CAL_FIRST_CELL)
        Dim lCell As Range: Set lCell = .Resize(.Worksheet.Rows.Count _
            - .Row + 1).Find("*", , xlFormulas, , , xlPrevious)
        If Not lCell Is Nothing Then
            rCount = lCell.Row - .Row + 1
            Set crg = .Resize(rCount)
        End If
    End With
    
    If crg Is Nothing Then
        MsgBox "No data found.", vbCritical
        Exit Sub
    End If
    
    Dim urg As Range: Set urg = crg.EntireRow.Columns(UNIT_COLUMN)
    
    Dim cData(), uData()
    
    If rCount = 1 Then
        ReDim cData(1 To 1, 1 To 1): cData(1, 1) = crg.Value
        ReDim uData(1 To 1, 1 To 1): uData(1, 1) = urg.Value
    Else
        cData = crg.Value
        uData = urg.Value
    End If
    
    Dim r As Long, rStr As String
    
    For r = 1 To rCount
        rStr = CStr(cData(r, 1))
        If Len(rStr) > 0 Then
            cData(r, 1) = rStr & DELIMITER & CStr(uData(r, 1))
        End If
    Next r
    
    Dim drg As Range: Set drg = crg.EntireRow.Columns(DST_COLUMN)
    
    drg.Value = cData
    
    MsgBox "Units appended.", vbInformation
    
End Sub