VBA - Concatenate - Dynamic Range - Row by Row

[MKader]

Hi All -

Need your help -

Was able to concatenate selected cells and value is placed in a designated cell -

How can I repeat the same process for all rows till the end of data in column -

Private Sub Trial_1_Click()
Dim CC As Range
Dim i As Integer
Dim TT As String
i = 0
TT = ""
For Each CC In Selection
If i = 0 Then
i = 1
End If
TT = TT & CC.Value & ""
Next
TT = Left(TT, Len(TT))
Range("P2").Value = TT
End Sub

Thanks,
MK

 

[Peter_SSs]

You didn't specifically state where to start the concatenation from. I've assumed the Active Cell.

Private Sub Trial_1_Click()
  Dim lr As Long
  
  lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
  Select Case lr - ActiveCell.Row
    Case 0: Range("P2").Value = ActiveCell.Value
    Case Is < 0: Range("P2").ClearContents
    Case Else: Range("P2").Value = Join(Application.Transpose(Range(ActiveCell, Cells(lr, ActiveCell.Column))), "")
  End Select
End Sub

 

[MKader]

Hi Peter -

Thanks for your reply -

The selection is basically by highlighting the cells in the Row then click the button which result in P2 the concatenated value of the selected cells -

Thanks,
MK

 

[Peter_SSs]

The selection is basically by highlighting the cells in the Row then click the button which result in P2 the concatenated value of the selected cells -

Isn't that what you code already did?
And aren't you looking for something different now?
Did you try my code?

 

[MKader]

Hi -

I did try the code - it didn't work -

Yea - my code does that by concatenating the highlighted cells into the equivalent P2 - what am trying to do is to replicate this automatically on the rest of the sheet based on the initial selection as if am fixating a selection and applying to the rest -

Thanks,
MK

 

[Peter_SSs]

Remember that we have not seen your sheet, nor know exactly what you are trying to do. All we have is what you tell us, so try to be clear & specific.

You initially said you wanted to "repeat the same process for all rows till the end of data in column". That's what I tried to give - concatenate everything from the first selected cell down the column until there was no more data. For example, in the sheet below, I had just cell K8 selected & ran the code. The results were put into P2.

Excel Workbook
	K	L	M	N	O	P
1
2	a					ghijklm
3	b
4	c
5	d
6	e
7	f
8	g
9	h
10	i
11	j
12	k
13	l
14	m
15
Concat

Now you say you want to "replicate this automatically on the rest of the sheet". Does that mean the same as the previous statement?


Can you give some specific examples of where you have data, what you (might) have selected, what the code should do and where it should put the results?
Perhaps you could even show us a small set of dummy data and expected results? My signature block below has a link with info about how you can do that (example above).


When suggested code doesn't work, try to give feedback that might help us work out what to change. For example..
- did the code produce an error?
- did it concatenate the wrong data?
- did if concatenate the right data but put it in the wrong place?
- did it do nothing?
- did it do something else?

 

[MKader]

Hi Peter -

Apologies if I am not that clear -

- did the code produce an error?
No
- did it concatenate the wrong data?
No
- did if concatenate the right data but put it in the wrong place?
No
- did it do nothing?
Yes
- did it do something else?
No


Maybe am not able to showcase what am trying to do -

When you select 2 & 3 then click the button - it result in 23
I am trying to have the rest apply the same - meaning the next cell would be 67, then 1011 and finally 1415

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Data 1[/TD]
[TD="align: center"]Data 2[/TD]
[TD="align: center"]Data 3[/TD]
[TD="align: center"]Data 4[/TD]
[TD="align: center"]Result[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]23[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]16[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks,
MK

 

[Peter_SSs]

OK, that's considerably clearer thanks. Though with that data and selection, my previous code should have produced 261014 in cell P2. Although that isn't what you wanted, it isn't nothing.

I'm assuming that you will always only have a single row and at least 2 columns selected. Give this one a try.

Private Sub Trial_1_Click()
  Dim lr As Long, r As Long
  
  Application.ScreenUpdating = False
  lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
  For r = 0 To lr - ActiveCell.Row
    Range("P" & Selection.Row).Offset(r).Value = Join(Application.Index(Selection.Offset(r).Value, 1, 0), "")
  Next r
  Application.ScreenUpdating = True
End Sub

 

[MKader]

Hi -

It did work - yet manually - meaning i have to select in each row the cells then click the button - :D

Thanks,
MK

 

[Peter_SSs]

It did work - yet manually - meaning i have to select in each row the cells then click the button - :D

Which code are you referring to now?