Concatenate cells in a Row

mmetzinger

Board Regular
Joined
Dec 30, 2010
Messages
61
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,953
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hi

Like this:

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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...

Code:
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...

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,959
Messages
5,525,899
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top