Search code examples
excelvba7

Excel VBA - How to find the last column with data for each row in a loop

I'm trying to create a dynamic concatenation script. Below is an image of a sample of the data:

Data

The goal is to loop through each row in column A and concatenate the values where the header is "Bundle Child x", separated by ";", and put the concatenated list in column A in the appropriate row. For example, the concatenated list in cell A2 should be: AVS5-OD101-GL;BT022204-GL;BT022204-GL;BT01201-GL;BT01201-GL;BT031141-GL

Here is the code I have so far:

Sub SpecialConcatenate()
Dim Sht As Worksheet
Dim lRow As Long
Dim lCol As Long
Dim iCell As Range

Set Sht = ActiveWorkbook.ActiveSheet
With Sht

    ' Get the last row of data in column B
        lRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(Abs(.Cells(.Rows.Count, 1).End(xlUp).Value <> ""), 0).Row
    
    ' Loop through cells in Column A and run concatenation
        For Each iCell In Range("A2:A" & lRow)
        ' Find the last column in current row
             lCol = .Cells(iCell.Row, .Columns.Count).End(xlToLeft).Column ' Issue is occurring here
            
        Next iCell
    
End With

End Sub

I'm not getting any errors, but lCol is not being identified correctly. I'm watching lCol as the code runs, and it's being set to 7 initially, but it's not updating as the loop goes through each row. 7 is accurate when iCell.Row = 2 and 3, but when iCell = 4, lCol should update to 4 and so on. Any suggestions as to why it's not updating?

Solution

  • The TEXTJOIN(delimiter,ignore_empty,text1,...) function can do everything you need. Just use TEXTJOIN function in cell A2 like this:

    =TEXTJOIN("-",TRUE,B2:N2)