Cocatenate Text in subsequent cells in a column

[Claudio1]

Can somebody help me to solve the below problem using VBA?

I want to concatenate cells A7, A8, B8, A9 and paste it to A7. Similarly concatenate A10, A11, A12, C12, A13, A14 and paste to A10. Concatenate A15, A16 and paste to A15. Keep A6 same and A17 same as original value.

Any help is appreciated. Thanks.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 111"]
<colgroup><col width="111"></colgroup><tbody>[TR]
[TD="width: 111"]Controller- 50 H
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]TestData
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]01/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]02/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<colgroup><col width="111"></colgroup><tbody>[TR]
[TD="width: 111"]03/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]RPM-50, PTO- 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<colgroup><col width="111"></colgroup><tbody>[TR]
[TD="width: 111"]10
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]CALIBRATED 101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0
[/TD]
[TD]11
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]ATTACHED LOAD 50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]T1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]BALANCE LOAD 10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]INSITU CALIBRATION 502[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]LOAD 500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]HALT 101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]T2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]SPEED 500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]SPEED TEST 60[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]MICRO CONTROLLER 1001
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]SPEED 40[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]XX/XX/XXXX 00:00:00 XX[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Claudio1]

Concatenate subsequent cells in a column in excel vba

[TABLE="width: 304"]
<colgroup><col width="304"></colgroup><tbody>[TR]
[TD="width: 304"]Can somebody help me with below problem using vba?

I would like to concatenate A7, A8, B8, A9 and paste to A7. Similarly concatenate A10, A11, A12, C12, A13, A14 and paste to A10. Concatenate A15, A16 and paste to A15.

Any help appreciated. Thanks.
[/TD]
[/TR]
</tbody>[/TABLE]

 

[JackDanIce]

Re: Concatenate subsequent cells in a column in excel vba

Try:

Sub macro1()

    Application.ScreenUpdating = False
    
    Cells(7, 1).valule = Cells(7, 1).Value & Cells(8, 1).Value & Cells(8, 2).Value & Cells(9, 1).Value
    Cells(10, 1).valule = Cells(10, 1).Value & Cells(11, 1).Value & Cells(12, 1).Value & Cells(12, 3).Value & Cells(13, 1).Value & Cells(14, 1).Value
    Cells(14, 1).Value = Cells(15, 1).Value & Cells(16, 1).Value
    
    Application.ScreenUpdating = True
    
End Sub

 

[Claudio1]

Re: Concatenate subsequent cells in a column in excel vba

Try:

Sub macro1()

    Application.ScreenUpdating = False
    
    Cells(7, 1).valule = Cells(7, 1).Value & Cells(8, 1).Value & Cells(8, 2).Value & Cells(9, 1).Value
    Cells(10, 1).valule = Cells(10, 1).Value & Cells(11, 1).Value & Cells(12, 1).Value & Cells(12, 3).Value & Cells(13, 1).Value & Cells(14, 1).Value
    Cells(14, 1).Value = Cells(15, 1).Value & Cells(16, 1).Value
    
    Application.ScreenUpdating = True
    
End Sub

Thanks for the info.

I have around 90 sheets where the cell address changes. Example row number 7 might be blank in some sheets. It is not possible to hardcode the cell positions. Looking for a dynamic solution.

 

[JackDanIce]

It's not clear what the pattern is from your post to understand how to code this dynamically.

 

[Rick Rothstein]

It's not clear what the pattern is from your post to understand how to code this dynamically.

In addition to what JackdanIce posted above, we cannot tell from your "picture" what the rows and columns are. Since it seems what cells are to be concatenated has something to do with what cells are filled with data, it is important that you make clear what the row and column designations are.