PasteSpecial Values

[Ron]

Hello all,

I'm having a problem with pasting values. I have a sheet that I'm
pasting values to from several other sheets, some columns have
formulas. Some of the formulas use =if(a3 = ""."".do something). I'm
copying and pasting values only of range ("A3:K42) with some of the
rows blank except for the formulas =if(a3 = ""."".do something).

I copy and paste one sheet at a time, but when I paste data from the
next sheet using End(xldown).offset(1,0) to position the cursor in the
right place to paste, it appears that there is actually something in
the cells I pasted as values because the code pastes values well below
the target cell. Does pasting values of a formula return a blank cell
as asked for in the formula using NullNUll or ""?

Thank you for your assistance, Ron

 

[joel]

If you had somethng like this

-if(A1=1,true,"")

The else condition would show up as something in a cells and the xldow
would see the cell as if there was data in the cell. Sometime if a cel
had data and the data was removed xldown will still thing there wa
something in the cell.

I find that is is better to delete an entire row or column rather tha
just clear a cell. As a worksheet gets used more and more the size o
the spreadhsett will grow (number of bytes in the file). Removing th
data in the cells will not reduce the file size unless some rows an
columns are deleted. The usedarea willalso grow and not get smalle
when data is removed from the last row(s) and columns(s).

The clearcontents method will completely clear out a cell so xldow
will think the cell is empty, but will not reduce the used area