macro help - extra contents of specific columns accross a row

[garyusenet]

I have a spreadsheet which consists of about 5125 rows and 2840
columns. I am using Excel 2007 in order to get access to the extra
columns. Now I need help creating a macro to automate a very tedius and
manual task. The first column in the spreadsheet is empty.

What I Need
===========
For every row I would like to extract the contents of certain columns
accross the row and copy to the first column of the row.

into the first column i would like to copy the contents of the
following columns accross the row. 6C = sixth column, 9C = 9th column
etc...

6C + " " + 7C + " " + 9C + " " + 35C + " " + 37C +
<carriage return>
+ 12C + " " + 14C + " " + 18C + " " + 70C + " " + 74C +
<carriage return>
etc...

I need the macro to be smart enough to continue adding carriage returns
and multiplies of the original columns, until it reaches the end of the
row.

I need to do this for every row.

Any advice please or code would be greatly appreciated.

 

[Kevin McCartney]

Hi Gary

You don't need a macro you can concatenate the cells together by using the
formula

=F2 & " " & G2 & " " &I2 & " " & AI2 & " " & AK2
& " " & L2 & " " & N2 & " " &R2 & " " &BR2 & " " &BV2

drag this down next to your data range. Since you've so many rows Excecl
will take sometime to calculate everytime you make a change or save the
workbook so once the calculation is complete for all rows, (you'll see the
the word calculate on the status bar with a percentage), so once the
calculation is complete, do a copy and pastes special, values for rows after
the first row, this way Excel only has to calculate the formula for one row
and you get to retain the formaula in case you need to make a change and
overwrite the other rows.

HTH
KM

 

[garyusenet]

Dear Kevin,

the reason i wanted is a macro is because i have thousands of
columns... so if i have to manually type out the formulae including all
over a thousand column references it will take me absolutely ages. Also
I need the data in a certain format (in a list) with those carriage
returns included.

I look forward to any replies.

Many Thanks,

Gary.