Concatenating Cells With A Variable Column Count

[Ark68]

I am hoping someone is able to help me complete this puzzle ... I know what I need to do, just not how to do it efficiently.

I have several rows in my database. Columns "R" and beyond may hold hold text values. What I need to do is combine all the values from column "R" to and including the last column in that row holding data.

For example =
if .cells( row, 18) is empty, nothing to concatenate
if .cells( row, 18) is the last column in that row with data than .cells( row, 18) remains unchanged
if .cells( row, 19) is the last column, .cells( row, 18) = .cells( row, 18) & "; " & .cells( row, 19).
if .cells( row, 22) is the last column with data, .cells( row, 18) = all the values joined from .cells( row, 18) to and including .cells( row, 22), each separated by "; "

I'm unsure how to do this efficiently.

This is where I have made it so far ...

    With worksheet       
        For i = 2 To lrow
            lcol = .Cells(i, .Columns.Count).End(xlToLeft).Column
            If lcol > 17 Then 'there are notes
                'code to combine values from column 18 on as needed
            End If
        Next i
    End with

 

[Ark68]

Hi Joe,

I finally encountered problematic data that triggered that error again, so I had an opportunity to test your suggestion. Adding ".value" resulted in an "Object required error."

As mentioned, this code isn't a total bust. It does work on the majority of data. In this case, as Fluff mentioned, I am wondering whether string length is an issue in this case. Column V's value (to which the other column values are added) is already 412 character's in length. WIth the contents of the following 3 columns having values, that number goes up to 456. I went to the raw data and changed the value in column V to something much shorter, less than 20 characters, and still got the error.

I eliminated that row of data (only for diagnostics, it won't be an option) and avoided the error. But it erred again on another row, this time though the value in column V was only 61 characters long. Column W had 5 characters, and column X had 280. The code intending to concatenate the values of all occupied columns following V (W and X in this case) for a combined total of 346 characters.

Fluff, are you still suspecting cell volume to be a potential source of the error? Although a cell, my understanding can hold up to 32k + of characters (really?) perhaps under these circumstances the rules are difference.

Here is all the data that this line errs on. Perhaps seeing the data can reveal the issue.

https://drive.google.com/file/d/13FIay7hc4Ufm_p7RxVw315p3ufJWe04e/view?usp=sharing

 

[Fluff]

Using Index in that manner is (I think) limited to 255 characters per cell.

 

[Ark68]

I agree that the values within the cells are playing havoc.
What other option do you think I could try out Fluff? This is a new concept for me and I'm not at that level of being able to think of an alternative without probably using a whole lot of if/thens.

 

[Fluff]

How about

Sub MG27Apr24()
Dim Rng As Range, Dn As Range, R As Variant
Dim Lst As Long, i As Long
Application.ScreenUpdating = False
Set Rng = Range("A2").CurrentRegion
For Each Dn In Rng.Columns(18).Cells
    Lst = Cells(Dn.Row, Columns.Count).End(xlToLeft).Column
    If Dn.Offset(, 1) <> "" Then
        R = Dn.Resize(, Lst - 17)
        For i = 2 To UBound(R, 2)
           Dn.Value = Dn.Value & "; " & R(1, i)
        Next i
        Dn.Offset(, 1).Resize(, Lst - 18).ClearContents
    End If
Next Dn
Application.ScreenUpdating = True
End Sub

 

[Ark68]

Hey Fluff! Fantastic job, thank you for your help! Your solution appears to have resolved the problem.
Very much appreciated!

 

[Fluff]

Your welcome & thanks for the feedback