Concatenation within a macro

  • Thread starter Thread starter Risky Dave
  • Start date Start date
R

Risky Dave

Hi,

I have a large data set that is automatically formatted by a macro. As part
of this macro I need to concatenate sets of three cells into one and format
the output.
Eg. I need to convert:
A B C
1 1Data1 1Data2 1Data3
2 2Data1 2Data2 2Data3
3 3Data1 3Data2 3Data3

To:
A
1 1Data1
1Data2
1Data3
2 2Data1
2Data2
2Data3
3 3Data1
3Data2
3Data3

The actual output cell can be pretty much anywhere - I can adjust this bit
to fit in with the rest of the formatting.

I can do this by setting up a separate sheet with loads of formulae, but as
there are several hundreds of lines involved (I have no way of knowing
exactly how many), each with about 100 cells, this soon makes the file size
excessively large (especially as there's a load of other stuff going on in
the overall workbook).

Can anyone supply a piece of code that I can drop into the rest of the macro
and will produce the output above?

Although I don't know how many times this needs to be carried out (because I
don't know the exact number of lines with data in them), I can set a high
(eg. 500 or 1000) limit for the number of lines to be formatted - it doesn't
matter if the macro attempts to format cells with no data in them.

Hope this makes sense :-)

If it makes any difference, this is in Office 2003.

TIA Dave
 
Sub put_um()
Set r = Range("D10")
For j = 1 To 3
v = ""
For i = 1 To 3
v = v & Cells(i, j).Value & Chr(10)
Next
r.Offset(j, 0).Value = v
Next
End Sub

will take your data and put the results in D11, D12, and D13.
 
Hi,

Thanks for the quick response.

This is nearly what I am after, but is giving me:

1Data1
2Data1
3Data1

1Data2
2Data2
3Data2

1Data3
2Data3
2Data3

ie. the columns concatenated into each cell, whereas I need the rows
concatenated vertically into each cell.

Thanks.


Dave
 
Use this version instead:

Sub put_um()
Set r = Range("D10")
For i = 1 To 3
v = ""
For j = 1 To 3
v = v & Cells(i, j).Value & Chr(10)
Next
r.Offset(i, 0).Value = v
Next
End Sub
 
Back
Top