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

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

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


<[email protected]> wrote in message news:T2xQd.65996$2p.48322@lakeread08...
> 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

No reason to loop if it is all one range

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

--
Regards,
Tom Ogilvy

<[email protected]> wrote in message news:T2xQd.65996$2p.48322@lakeread08...
> 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

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

In article <[email protected]>,
"Tom Ogilvy" <[email protected]> wrote:

> No reason to loop if it is all one range
>
> with Range("A1:Y5300")
> .Formula = .Value
> End With

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

In article <[email protected]>,
"Steve" <[email protected]> wrote:

> Range(yourRange).Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues
>
>
> <[email protected]> wrote in message news:T2xQd.65996$2p.48322@lakeread08...
> > 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
>
>

Think your back to looping.

--
Regards,
Tom Ogilvy


<[email protected]> wrote in message news:EnRQd.68019$2p.4743@lakeread08...
> 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
>
> In article <[email protected]>,
> "Tom Ogilvy" <[email protected]> wrote:
>
> > No reason to loop if it is all one range
> >
> > with Range("A1:Y5300")
> > .Formula = .Value
> > End With

Tom -

Looping it was. And a custom number format. I needed the end result to
be a tabbed text file. Saving as text saved with it the custom format,
something I had't appreciated.

Thanks for your help.

....best, Hash

In article <[email protected]>,
"Tom Ogilvy" <[email protected]> wrote:

> Think your back to looping.
>
> --
> Regards,
> Tom Ogilvy
>
>
> <[email protected]> wrote in message news:EnRQd.68019$2p.4743@lakeread08...
> > 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
> >
> > In article <[email protected]>,
> > "Tom Ogilvy" <[email protected]> wrote:
> >
> > > No reason to loop if it is all one range
> > >
> > > with Range("A1:Y5300")
> > > .Formula = .Value
> > > End With
>
>