false blanks
 

Hi people,

I made a copy/paste special values from one sheet (formulas) to another.
These formulas give me either a numeric value or blank ("") cell.
However, the blanks that I copied/pasted are in fact "false blanks": if I
use xlDirection, it goes to the very end of the pasted range, even if in
between the cells are equal to "".
Also, if I check the "false blank" cells with the =ISBLANK(), it gives me
"FALSE".

Do you know how to convert these "false blanks" to the normal blanks? (I
know I can do it manually by going through the range and pressing "delete"
key where needed, but I would rather look for a VBA solution, if it
exists...)

Your help would be much appreciated,
Thanks,

Mark

false blanks
 

try testing for length if you don't really have any one character text
len(mycell)<2 or 1

--
Don Guillett
SalesAid Software

"markx" wrote in message
...
Hi people,

I made a copy/paste special values from one sheet (formulas) to another.
These formulas give me either a numeric value or blank ("") cell.
However, the blanks that I copied/pasted are in fact "false blanks": if I
use xlDirection, it goes to the very end of the pasted range, even if in
between the cells are equal to "".
Also, if I check the "false blank" cells with the =ISBLANK(), it gives me
"FALSE".

Do you know how to convert these "false blanks" to the normal blanks? (I
know I can do it manually by going through the range and pressing "delete"
key where needed, but I would rather look for a VBA solution, if it
exists...)

Your help would be much appreciated,
Thanks,

Mark

false blanks
 

Or put this immediately after the PasteSpecial line

for each c in selection.cells
if c.value = "" then c.value = empty
next


Col


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=546458

false blanks
 

Hi, I checked these cells for characters, and once again it confirms me that
there is neither space nor character inside...
It's really very strange, but I'm almost sure that it has something to do
with copy/paste special/value of formulas with result equal to blank...

Mark


"Don Guillett" wrote in message
...
try testing for length if you don't really have any one character text
len(mycell)<2 or 1

--
Don Guillett
SalesAid Software

"markx" wrote in message
...
Hi people,

I made a copy/paste special values from one sheet (formulas) to another.
These formulas give me either a numeric value or blank ("") cell.
However, the blanks that I copied/pasted are in fact "false blanks": if I
use xlDirection, it goes to the very end of the pasted range, even if in
between the cells are equal to "".
Also, if I check the "false blank" cells with the =ISBLANK(), it gives me
"FALSE".

Do you know how to convert these "false blanks" to the normal blanks? (I
know I can do it manually by going through the range and pressing
"delete" key where needed, but I would rather look for a VBA solution, if
it exists...)

Your help would be much appreciated,
Thanks,

Mark

false blanks
 

Thanks colofnature,
Your solution works perfectly!
Regards,
Mark


"colofnature"
wrote in message
...

Or put this immediately after the PasteSpecial line

for each c in selection.cells
if c.value = "" then c.value = empty
next


Col


--
colofnature
------------------------------------------------------------------------
colofnature's Profile:
http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=546458

false blanks
 

select the range, do
Edit=Replace
Replace What: Leave Blank
Replace With: $$$$

then reverse

Edit=Replace
Replace What: $$$$
Replace With: leave blank

clearly you can do this with code as well.

--
Regards,
Tom Ogilvy


"markx" wrote in message
...
Hi people,

I made a copy/paste special values from one sheet (formulas) to another.
These formulas give me either a numeric value or blank ("") cell.
However, the blanks that I copied/pasted are in fact "false blanks": if I
use xlDirection, it goes to the very end of the pasted range, even if in
between the cells are equal to "".
Also, if I check the "false blank" cells with the =ISBLANK(), it gives me
"FALSE".

Do you know how to convert these "false blanks" to the normal blanks? (I
know I can do it manually by going through the range and pressing "delete"
key where needed, but I would rather look for a VBA solution, if it
exists...)

Your help would be much appreciated,
Thanks,

Mark