Excel concatenate unknown number of columns 1

[waubain]

I need to concatentate an unknown number of columns that always start at Column L to Column ?? and then place the new string back into column L.

When I was using a test file the biggest record went from column L to P. And wrote this code.

Sub ConCatSig()

Dim objExcel As Excel.Application ' Declares Variable
Dim objWB As Excel.Workbook ' Declares Variable
Dim lngLastRow As Long
Dim lngLastColumn

lngLastRow = objWB.Worksheets(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'lngLastColumn = objWB.Worksheets(1).Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    objWB.Worksheets(1).Range("Q2:Q" & lngLastRow).value = "=L2:L" & lngLastRow & "&"" ""&" & "M2:M" & lngLastRow _
                                                            & "&"" ""&" & "N2:N" & lngLastRow _
                                                            & "&"" ""&" & "O2:O" & lngLastRow _
                                                            & "&"" ""&" & "P2:P" & lngLastRow
    objWB.Worksheets(1).Range("Q2:Q" & lngLastRow).Copy
    objWB.Worksheets(1).Range("R2:R" & lngLastRow).PasteSpecial xlPasteValues
    objWB.Worksheets(1).Range("L:Q").EntireColumn.Delete Shift:=xlShiftToLeft
    
End Sub

In production I found one record that went from column L to V, so I need something flexible. I can count columns, but cannot find an example of concatenating multiple columns. I thought about somehow concatenating column L to Column.Offset(, lngLastColumn - 1).

Any help would be appreciated.

You don't know what you don't know...

 

[SkipVought]

hi,

maybe this?

Sub ConCatSig()
    Dim r1 As Range
    Dim r2 As Range
'assuming that your data starts in row 1
    For Each r1 In Range(Cells(1, "L"), Cells(1, "L").End(xlDown))
        For Each r2 In Range(r1.Offset(0, 1), r1.Offset(0, 1).End(xlToRight))
            r1.Value = r1.Value & r2.Value
            If CStr(r2.Offset(0, 1).Value) = "" Then Exit For
        Next
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[waubain]

Thanks Skip.



You don't know what you don't know...