Select, copy and paste - VBA code

Posted by Matthew Mills on October 19, 2000 3:56 AM

I have created a formula in first Row of Column 3 which has concatenated the two text item in Columns 1 and 2. Columns 1 and 2 have an equal but unknown number of rows in them and I want to copy the formula from the first row of Column 3 and paste it ito the rest of Column 3.

C1 C2 C3
Matthew Mills R1C1&" "&R1C2
Liz Mastin Copy and paste the above
Melanie Kemp Copy and paste the above

I can do thi row by row but the spreadsheet is 22,000 rows long and this takes ages. There MUST be a way of doing thgis more efficiently.

Any ideas ?

Thanks

Matthew

Posted by JAF on October 19, 2000 4:12 AM

Assuming you have no empty rows in columns 1 or 2 you can do the following:

If you select the cell that contains your formula and move the cursor over the small black square at the bottom right hand corner of that cell, the cursor will change from the standard thick white cross into a thin black cross. If you then double click with the left mouse button, your formula will automatically be copied down to the last continuous row of data.

It doesn't matter of you have 20 rows or 20,000 rows, the process is the same (but it will obviously take longer to Calculate the more rows you have).

Hope this helps.
JAF

Posted by Celia on October 19, 2000 4:49 AM

Or if you need to do it with VBA code :-

Range([A1], [A65536].End(xlUp)).Offset(0, 2).Formula = "=A1&"" ""&B1"

This code puts the formula into column 3 from row 1 down to the last row with data in column A.

Celia