fast way to copy formatting in excel

Question

I have two bits of code. First a standard copy paste from cell A to cell B

Sheets(sheet_).Cells(x, 1).Copy Destination:=Sheets("Output").Cells(startrow, 2)

I can do almost the same using

Sheets("Output").Cells(startrow, 2) = Sheets(sheet_).Cells(x, 1)

Now this second method is much faster, avoiding copying to clipboard and pasting again. However it does not copy across the formatting as the first method does. The Second version is almost instant to copy 500 lines, while the first method adds about 5 seconds to the time. And the final version could be upwards of 5000 cells.

So my question can the second line be altered to included the cell formatting (mainly font colour) while still staying fast.

Ideally I would like to be able to copy the cell values to a array/list along with the font formatting so I can do further sorting and operations on them before I "paste" them back on to the worksheet..

So my ideal solution would be some thing like

for x = 0 to 5000
array(x) = Sheets(sheet_).Cells(x, 1) 'including formatting
next

for x = 0 to 5000
Sheets("Output").Cells(x, 1)
next

is it possible to use RTF strings in VBA or is that only possible in vb.net, etc.

Answer*

Just to see how my origianl method and new method compar, here are the results or before and after

New code = 65msec

Sheets("Output").Cells(startrow, 2) = Sheets(sheet_).Cells(x, 1)
Sheets("Output").Range("B" & startrow).Font.ColorIndex = Sheets(sheet_).Range("A" & x).Font.ColorIndex 'copy font colour as well

Old code = 1296msec

'Sheets("Output").Cells(startrow, 2).Value = Sheets(sheet_).Cells(x, 1)
'Sheets(sheet_).Cells(x, 1).Copy
'Sheets("Output").Cells(startrow, 2).PasteSpecial (xlPasteFormats)
'Application.CutCopyMode = False

Answer 1

For me, you can't. But if that suits your needs, you could have speed and formatting by copying the whole range at once, instead of looping:

range("B2:B5002").Copy Destination:=Sheets("Output").Cells(startrow, 2)

And, by the way, you can build a custom range string, like Range("B2:B4, B6, B11:B18")

---

edit: if your source is "sparse", can't you just format the destination at once when the copy is finished ?

Answer 2

You could have simply used Range("x1").value(11) something like below:

Sheets("Output").Range("$A$1:$A$500").value(11) =  Sheets(sheet_).Range("$A$1:$A$500").value(11)

range has default property "Value" plus value can have 3 optional orguments 10,11,12. 11 is what you need to tansfer both value and formats. It doesn't use clipboard so it is faster.- Durgesh