Concatenating of dynamic cell range. HELP

[nithishvinolin]

Hi,

I need a help in developing a macro.
I have a example. I have 4 columns. first 3 columns ID, C1, C2 have the input data. Under a ID we have multiple number of rows. In this example we have 4 rows for ID1. But in real time it can be any number of rows under a single ID. When I click a button the values under ID 1 should get concatenated and stored under the Concatenate column.
I tried some macros but i cant get the logic behind finding the dynamically changing row counts under single ID. Any kind of help will be appreciated. Thanks in advance.


[TABLE="width: 297"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]C1[/TD]
[TD]C2[/TD]
[TD]Concatenate [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]12345678[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]91011121314[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]15161718[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG04Jun31
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dn.Offset(, -1) = vbNullString [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] Temp = Dn
    Temp.Offset(, 2) = Temp.Offset(, 2) & Dn & Dn.Offset(, 1)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[nithishvinolin]

Hi Mick,

Thanks a lot. It worked. You are a genius. I was kicking my brains for this.

One kind help. Can you explain the logic behind the vba? I am feeling tough to understand. I am very new to macro. It will be so much useful for me to proceed further.


Thanks,
Nithish

 

[MickG]

This may help !!

[COLOR="Navy"]Sub[/COLOR] MG04Jun13
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] Range
'[COLOR="Green"][B]set Rng = range of cells in column "B" that have data[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))


'[COLOR="Green"][B]Loop through Rng[/B][/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
   
   '[COLOR="Green"][B]If columns "A" Value is not "Blank" then set that Cell to range variable "Temp"[/B][/COLOR]
   '[COLOR="Green"][B]This range (Temp) will stay the same until the loop find another cell in[/B][/COLOR]
   '[COLOR="Green"][B]column "A" with a value.[/B][/COLOR]
    [COLOR="Navy"]If[/COLOR] Not Dn.Offset(, -1) = vbNullString [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] Temp = Dn
    
    
    '[COLOR="Green"][B]Each value in the loop is Concatenated in "Temp" Range offset(,2)= Column "D"[/B][/COLOR]
    Temp.Offset(, 2) = Temp.Offset(, 2) & Dn & Dn.Offset(, 1)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[nithishvinolin]

It is really helpful. thanks again for explaining the code.

 

[MickG]

You're welcome

 

[gacc99]

How do you do this for several rows in Excel? I need a formula as well?