Concatenate row with variable columns where some have no data

[v-joro]

Hi,

To start off, my question is very similar to the one at this location: http://www.mrexcel.com/forum/excel-questions/553169-concatenate-row-variable-number-columns.html

This is the provided solution that I've been trying to use:

Sub ConCatFromColumnC()
  Dim X As Long, LastRow As Long, LastCol As Long
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = StartRow To LastRow
    LastCol = Cells(X, Columns.Count).End(xlToLeft).Column
    If LastCol = 3 Then
      Cells(X, "B").Value = Cells(X, "C").Value
    Else
      Cells(X, "B").Value = Join(Application.Index(Range(Cells(X, "C"), Cells(X, LastCol)).Value, 1, 0), ", ")
    End If
  Next
End Sub

However, the problem is that when I use the solution provided, I receive the error: "Run-time error '13': Type mismatch", and it highlights the line immediately below the "Else" statement. I believe this is because some of the rows in my data do not have data to be concatenated (or, I may be adapting the solution incorrectly by only changing the "B" and "C" values to "E" and "F").

Example:

[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD]Row1[/TD]
[TD][/TD]
[TD]Data1[/TD]
[TD]Data4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row2[/TD]
[TD][/TD]
[TD]Data1[/TD]
[TD]Data2[/TD]
[TD]Data3[/TD]
[/TR]
[TR]
[TD]Row3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row4[/TD]
[TD][/TD]
[TD]Data2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row5[/TD]
[TD][/TD]
[TD]Data1[/TD]
[TD]Data3[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


I would like the second column in the example (which is column E in my spreadsheet) to concatenate all cells that contain data to the right of it. Some of these are as long as 200 cells, but they will always be horizontally adjacent to each other, because I've removed blank cells and shifted the data left.

If it helps, each row will contain a counter to show how many cells are populated in the row, so the macro could reference that if needed.

Thank you in advance!

 

[Momentman]

So for the table shown above, what do you expect to see in column E of row 1, "Data1 Data4"? 'cos I have not looked at the macro above

 

[v-joro]

Guess I forgot to provide that. Yes, you are correct. Here's the updated example:

[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD]Col. D
[/TD]
[TD]Col. E
[/TD]
[TD]Col. F
[/TD]
[TD]Col. G
[/TD]
[TD]Col. H
[/TD]
[/TR]
[TR]
[TD]Row 1
[/TD]
[TD]Data 1, Data 4
[/TD]
[TD]Data 1
[/TD]
[TD]Data 4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2
[/TD]
[TD]Data 1, Data 2, Data 3
[/TD]
[TD]Data 1
[/TD]
[TD]Data 2
[/TD]
[TD]Data 3
[/TD]
[/TR]
[TR]
[TD]Row 3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 4
[/TD]
[TD]Data 2
[/TD]
[TD]Data 2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 5
[/TD]
[TD]Data 1, Data 3
[/TD]
[TD]Data 1
[/TD]
[TD]Data 3
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Also, mild revision to the code below, I had a couple of incorrect references that I just noticed and are now fixed (still getting the same run-time error though).

  Dim X As Long, LastRow As Long, LastCol As Long
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "D").End(xlUp).Row
  For X = StartRow To LastRow
    LastCol = Cells(X, Columns.Count).End(xlToLeft).Column
    If LastCol = 6 Then
      Cells(X, "E").Value = Cells(X, "F").Value
    Else
      Cells(X, "E").Value = Join(Application.Index(Range(Cells(X, "F"), Cells(X, LastCol)).Value, 1, 0), "; ")
    End If
  Next

 

[Momentman]

JUst wrote this, Please give it a try

Sub concatenatesum()
    lastrow = Range("d1").End(xlDown).Row
    
    For I = 2 To lastrow
        lastcol = Range("XFD" & I).End(xlToLeft).Column
        If lastcol <= 5 Then
            Cells(I, "E").Value = ""
        Else
            msg = ""
            For j = 6 To lastcol
                If Cells(I, j).Value <> "" And msg <> "" Then
                    msg = msg & ", " & Cells(I, j)
                Else
                    msg = msg & Cells(I, j)
                End If
                               
            Next j
            Cells(I, "E").Value = msg
        End If
    Next I
End Sub

 

[Rick Rothstein]

Hi,

To start off, my question is very similar to the one at this location: http://www.mrexcel.com/forum/excel-questions/553169-concatenate-row-variable-number-columns.html

This is the provided solution that I've been trying to use:

Sub ConCatFromColumnC()
  Dim X As Long, LastRow As Long, LastCol As Long
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, "[B][COLOR=#FF0000]D[/COLOR][/B]").End(xlUp).Row
  For X = StartRow To LastRow
    LastCol = Cells(X, Columns.Count).End(xlToLeft).Column
    If LastCol = [COLOR=#FF0000][B]6[/B][/COLOR] Then
      Cells(X, "[COLOR=#FF0000][B]E[/B][/COLOR]").Value = Cells(X, "[COLOR=#FF0000][B]C[/B][/COLOR]").Value
    Else
      Cells(X, "[COLOR=#FF0000][B]E[/B][/COLOR]").Value = Join(Application.Index(Range(Cells(X, "[COLOR=#FF0000][B]F[/B][/COLOR]"), Cells(X, LastCol)).Value, 1, 0), ", ")
    End If
  Next
End Sub

However, the problem is that when I use the solution provided, I receive the error: "Run-time error '13': Type mismatch", and it highlights the line immediately below the "Else" statement. I believe this is because some of the rows in my data do not have data to be concatenated (or, I may be adapting the solution incorrectly by only changing the "B" and "C" values to "E" and "F").

Example:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Row1[/TD]
[TD][/TD]
[TD]Data1[/TD]
[TD]Data4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row2[/TD]
[TD][/TD]
[TD]Data1[/TD]
[TD]Data2[/TD]
[TD]Data3[/TD]
[/TR]
[TR]
[TD]Row3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row4[/TD]
[TD][/TD]
[TD]Data2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row5[/TD]
[TD][/TD]
[TD]Data1[/TD]
[TD]Data3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I would like the second column in the example (which is column E in my spreadsheet) to concatenate all cells that contain data to the right of it. Some of these are as long as 200 cells, but they will always be horizontally adjacent to each other, because I've removed blank cells and shifted the data left.

If it helps, each row will contain a counter to show how many cells are populated in the row, so the macro could reference that if needed.

Thank you in advance!

Here is the code you tried to use modified for your setup as opposed to the setup the code you copied was designed for...

Sub ConCatFromColumnC()
  Dim X As Long, LastRow As Long, LastCol As Long
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, "D").End(xlUp).Row
  For X = StartRow To LastRow
    LastCol = Cells(X, Columns.Count).End(xlToLeft).Column
    If LastCol = 6 Then
      Cells(X, "E").Value = Cells(X, "C").Value
    Else
      Cells(X, "E").Value = Join(Application.Index(Range(Cells(X, "F"), Cells(X, LastCol)).Value, 1, 0), ", ")
    End If
  Next
End Sub

I highlighted in red all the changes I had to make to adapt the code to your setup. The only change I am not sure of is the one in the 3rd line of code where I use "D" as the column to calculate the LastRow... that column needs to be a column that is guaranteed to contain the last piece of data on the worksheet, so if D is not correct, then change the letter designation to the column containing data down to the end of the data on the sheet.

 

[v-joro]

Hi,

Momentman: That worked perfectly, thank you so much!

Rick: Thank you for helping me with adapting your code; it does work, but only if there aren't blanks between the data it's pulling. So, in the example, Row 3 causes it to error. However, the code posted by Momentman worked perfectly, and didn't encounter any errors, so I've got what I need now.

Thank you both so much for your help! I'd been trying to figure this out for a while, you've saved me a lot of time.

 

[Rick Rothstein]

Rick: Thank you for helping me with adapting your code; it does work, but only if there aren't blanks between the data it's pulling. So, in the example, Row 3 causes it to error. However, the code posted by Momentman worked perfectly, and didn't encounter any errors, so I've got what I need now.

I know you have an answer from Momentman, but I still wanted to make my code work for you (mainly to keep the thread accurate for possible future viewers), so here is my fixed code...

Sub ConCatFromColumnC()
  Dim X As Long, LastRow As Long, LastCol As Long
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, "D").End(xlUp).Row
  For X = StartRow To LastRow
    LastCol = Cells(X, Columns.Count).End(xlToLeft).Column
    If LastCol = 6 Then
      Cells(X, "E").Value = Cells(X, "F").Value
    ElseIf LastCol > 6 Then
      Cells(X, "E").Value = Join(Application.Index(Range(Cells(X, "F"), Cells(X, LastCol)).Value, 1, 0), ", ")
    End If
  Next
End Sub

 

[v-joro]

I know you have an answer from Momentman, but I still wanted to make my code work for you (mainly to keep the thread accurate for possible future viewers), so here is my fixed code...

Hi Rick,

I tested your new version, and can confirm that it works with my data. I may actually use this instead, because I understand what your code is doing, which will allow for easier troubleshooting. Thank you!