Excel-Concatenation

[rahulgodha]

Hi Friends,

I have 10,000 rows and unlimited data in columns. Two parameters in adjacent columns and are repeating after 13 unwanted columns.

I want to concatenate these adjacent columns of a row separated by space

eg:

concatenate(B2,C2,"",P2,Q2,"",AC,AD,"",....)

But I dont know upto which column the data is present.

Can you suggest me a macro which concatenates data upto blank column in one cell of a row and same is continued for first 10,000 rows.
Thanks a lot for help !

 

[venkat1926]

try this macro(depends upon you data)




'concatenate(B2,C2,"",P2,Q2,"",AC,AD,"",....)

Sub test()
Dim r As Range, x As String
x = ""
Set r = Range("B2")
x = x & r & r.Offset(0, 1)
Do
Set r = r.Offset(0, 14)
x = x & " " & r & r.Offset(0, 1)
Set r = r.Offset(0, 14)
If r.Column > Cells(2, Columns.Count).End(xlToLeft).Column Then Exit Do
Loop
MsgBox x
End Sub

 

[rahulgodha]

Thanks venkat for quick response.

The vba was indeed very helpful. I am getting a pop up box with the output. Is it possible to get the output
1. in column A2
2. and similarly output for other rows (approx 10K rows)

Basically, For my further calculation I need output in column A2 of every row.

Thanks again

 

[venkat1926]

try this modification of the macro
see comments in caps within the macro

Sub test()
Dim r As Range, x As String
Dim j As Integer, k As Integer
Application.ScreenUpdating = False
x = ""
Worksheets("sheet1").Activate
j = Range("B2").End(xlDown).Row
'ASSUME THRE IS NO BLANK ROW ATLEAST IN COLUMN B
For k = 2 To j
'Set r = Range("B2")
Set r = Cells(k, 2)
x = x & r & r.Offset(0, 1)
Do
Set r = r.Offset(0, 14)
x = x & " " & r & r.Offset(0, 1)
Set r = r.Offset(0, 14)
If r.Column > Cells(k, Columns.Count).End(xlToLeft).Column Then Exit Do
Loop
'MsgBox x
Cells(k, 1) = x
x = ""
Next k
Application.ScreenUpdating = True
MsgBox "macro over"
End Sub