Copying a range to the row below
 

I want to copy a range of cells in one row to the next empty row.

I do it like this:

Worksheets("Ledger").Activate

i = GetLastRow() ' Get last row number from function (getlastrow)

Set MyRange = Range("A" & i & ":L" & i) ' Set range to last row

MyRange.Copy Destination:=Worksheets _
("Ledger").Range("A" & i + 1 & ":L" & i + 1)

That works fine (though there could be a better way)

However, if I then copy the same range to a different worksheet, where
the cells on the source range are empty, on the pasted range they
show up as O's

Is there a way to avoid this without having to then cycle through all
of the pasted range clearing the cells with 0's ?

Trevor

Copying a range to the row below
 

I think you have really camouflaged your question, Trevor. It has nothing
to do with your copying macro. Isn't it really?:

"My formulas return zeros when they reference empty cells. How do I get
them to return blanks?"

You have two choices if that's your question. One is to turn off the
display of zeros on the worksheet generally with Tools, Options, View, Zero
Values. The other is to revise the formulas that you are copying not to
return zeros. A simple example:

=IF(A1="","",A1)

In your case you'd have to do something like: =IF(<< Your Formula
=0,"",<< Your Formula )

--
Jim Rech
Excel MVP

Copying a range to the row below
 

Hi Jim,

On reflection I think you are right. The macro acheives what I want,
but the results are not as expected. However, I see that it's not the
macro's fault, but mine for not understanding how it works on cut/
paste.

What I really wanted to avoid was the IF() function as I have to test
every item A-F everytime which must be a burden on processing.

On the other hand, I hadn't thought about turning off the display of
zero's which pretty much does exactly what I want. I wonder if it
extends to printing?

Later: Found that this worked using the custom number format:

[0]#,##0.00;

Thanks,

Trevor





On Mon, 20 Oct 2003 13:28:08 -0400, "Jim Rech"
wrote:

I think you have really camouflaged your question, Trevor. It has nothing
to do with your copying macro. Isn't it really?:

"My formulas return zeros when they reference empty cells. How do I get
them to return blanks?"

You have two choices if that's your question. One is to turn off the
display of zeros on the worksheet generally with Tools, Options, View, Zero
Values. The other is to revise the formulas that you are copying not to
return zeros. A simple example:

=IF(A1="","",A1)

In your case you'd have to do something like: =IF(<< Your Formula
=0,"",<< Your Formula )