Concatenate cells in a Row

[mmetzinger]

I need a way to concatenate cells in a given row.

So let's say we have.

Dim Row as string
Row = Activecell.Row

I need something like
Dim ConValue as string
ConValue = CONCATENATE("A" & Row, "B" & Row)

I know the code above isn't right. I just can't figure out how to give Excel the variable row. I don't want to mess with the whole RC from cursor method.

Thanks,
Matt

 

[wigi]

Hi

Like this:

Dim ConValue as String
Dim lRow as [COLOR=#ff0000]Long[/COLOR]
lRow = Activecell.Row
ConValue = [COLOR=#ff0000]Range("A" & lRow).Value & Range("B" & lRow).Value[/COLOR]

 

[Rick Rothstein]

I need a way to concatenate cells in a given row.

So let's say we have.

Dim Row as string
Row = Activecell.Row

I need something like
Dim ConValue as string
ConValue = CONCATENATE("A" & Row, "B" & Row)

I know the code above isn't right. I just can't figure out how to give Excel the variable row. I don't want to mess with the whole RC from cursor method.

If you only want to concatenate the two cells you show...

ConValue = Cells(Row, "A").Value & Cells(Row, "B").Value

If, on the other hand, you want to concatenate all the cells from Column A to the last used column on the row...

Row = 2
Delimiter = ", "
ConValue = Application.Trim(Join(Application.Index(Range(Cells(Row, "A"), Cells(Row, Columns.Count).End(xlToLeft)).Value, 1, 0), Delimiter))

I used a comma/space for the delimiter in the above just to show you how one could be used; however, your stated question showed no delimiter for the concatenation, so you would set that variable equal to "" in order to have nothing between the concatenated cell values.