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]

One hurdle that I have encountered that is affecting all these solutions, is that some rows have blank cells before column 18.
So this line, and variations of it in other solutions,

lcol = Cells(i, Columns.Count).End(xlToLeft).Column

prevent my objective to be met. It's picking up empty columns before I need to find them.
I need to alter this line so it looks for columns only after column 14 (changed from 18 after I've removed some columns)

Trying this ...

    For i = 2 To lrow
            lCol = .Range(.Cells(i, 14), .Cells(i, .Columns.Count)).End(xlToLeft).Column
            If lCol > 14 Then 'there are notes
                .Cells(i, 14) = Join(Application.Index(.Range(.Cells(i, 14), .Cells(i, lCol)).Value, 1, 0), ";")
            End If
        Next i

... still didn't work, it was catching columns before empty ones before column 14 (ie column 9 when cells(i, 10) are empty.

I need to find how to look at columns after column 14.

 

[Fluff]

This line

lcol = Cells(i, Columns.Count).End(xlToLeft).Column

will pick up the last used column, so if you have data in column 18, it doesn't matter if columns before that are empty, as it will return 18

 

[Ark68]

Ah, that makes sense. I refreshed my data and your contribution works Fluff, thank you.

Joe, thank you for your contribution. I have no doubts your addition to Fluff's solution will work. I haven't an opportunity to test it with my data, as that scenario will never come up considering how the dataset is built. But it's interesting to see that Fluff's solution would include the blank column cells that would result in a series of delimiters. I overlooked that possibility and it would have been a problem in a different environment..

Mick, I was particularly interested in your code as it introduced some new concepts for me. With my spelling mistake corrected (thanks Mark for helping tired eyes) it too gave great results.

And although I didn't test sandboy's soultion, from looking at it, I anticipate it would work, however, it didn't address the dynamic nature of the last column and the need to concatenate any number of columns worth of data. That approach was more static, but I feel easily made dynamic by perhaps simply changing ...

        ElseIf lcol = 22 Then
            For j = 18 To 22
            .Cells(i, 18) = .Cells(i, 18) & "; " & .Cells(i, j)
            Next
        End If

to

       Else
            For j = 18 To lcol
               no_cols = lcol - 18
               for t = 1 to no_cols               
                  .Cells(i, 18) = .Cells(i, 18) & "; " & .Cells(i, j)
                  t=t+1
               next t
            Next j
        End If

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Ark68]

I am revisiting this thread because I have ran into an error that I'm unable to isolate ...

    Set Rng = .Range("A2").CurrentRegion
        For Each dn In Rng.Columns(22).Cells
            Lst = .Cells(dn.row, .Columns.count).End(xlToLeft).Column
            If dn.offset(, 1) <> "" Then
                r = dn.Resize(, Lst - 21)
                dn.Value = Join(Application.index(r, 0, 0), ", ")
                dn.offset(, 1).Resize(, Lst - 22).ClearContents
            End If
        Next dn

The line in red is giving me a "Type mismatch". The code for the most part works, not causing any errors on other lines.
I believe the line that it is encountering is line 413 (dn.row). Column 22 has text, as does columns 23 through 26. The idea with the code is to concatenate the values of column 23 through 26 to the end of the value in Column 22.

Can anyone suggest why I might be getting an error? Is there something in the data that I should look at? I know the value in column 22 is 412 characters. Thinking that may be beyond a working limit, I reduced it to just 3 as a test but still had that error.

 

[JoeMo]

Not sure what's at issue, but this is simple enough to try:

Replace the faulty line with this:

dn.Value = Join(Application.index(r.Value, 0, 0), ", ")

 

[Ark68]

Hi Joe ... I just want to acknowledge your contribution. In my last round of testing, I hadn't encountered this problem. Not sure what conditions were different then than now that is preventing the problem ... but at least have an option to test when / if it does.

 

[Fluff]

Something else to look at is if any of the cells in that row contain long strings.

 

[JoeMo]

Hi Joe ... I just want to acknowledge your contribution. In my last round of testing, I hadn't encountered this problem. Not sure what conditions were different then than now that is preventing the problem ... but at least have an option to test when / if it does.

Thanks for the acknowledgement, but just curious, did you test the contribution? In my experience, combining the Index worksheet function with VBA's Join function always returns a 'Type mismatch' if you don't explicitly use the .Value property of the range argument in the Index function.

 

[Ark68]

Hi Joe, no I hadn't tested that yet because everything seems toi be working. I haven't run into the scenario where I am am getting that error to know if it makes a difference yet. I think Fluff may be on to something. Even when I was getting that error, I was only getting it with one row of data, all the other rows needing concatenating worked. The row that it erred on just so happened to have a value 412 characters in length as mentioned in my OP. But in my testing thinking that may be the problem, I changed that value to just 5 or 6 characters and it still erred.

It may be working now because the data doesn't have an cells with huge string lengths.