Quickest way to copy then Paste Special-Values in place?

[Hash]

All -

I'm cycling through a large range (~25x5300 cells) and setting .formula
= .value to accomplish paste-special-values. There has to be a quicker
way to copy and paste in place.

When I repeat the macro recoder code, it somehow screws up in the
spreadsheet, and I get ghost cells on my screen--cells overlaying cells
that aren't really there.

Anyway, - what's the best way to do this?

....best, Hash

 

[Steve]

Range(yourRange).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues

 

[Tom Ogilvy]

No reason to loop if it is all one range

with Range("A1:Y5300")
.Formula = .Value
End With

 

[Hash]

Tom -

Thanks. They were. Works fine except I now need to split it up. One
columnn was read-in as text in a date format that Excel now turns into a
date-number. Comes in as 2005-02-16 and becomes 2/16/05. Changing the
number format doesn't hack it. I need it to stay as text.

..Formula = Format(.value, "yyyy-mm-dd") gives a type mismatch.

Will .Formula = "='" & .value work? Or do I have to pass .value to an
intermediary Date variable? or some 3rd choice? I'll accept some
overhead here to stay as a text-string.

....best, Hash

 

[Hash]

Steve -

That's the code that gave the ghosting. Don't have a clue as to why,
but it was repeatable for this size array. Didn't happen on smaller
arrays used as guinea pigs for the macro recorder.

Thanks for chiming in.

....best, Hash

 

[Tom Ogilvy]

Think your back to looping.