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

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

--
Don Guillett
SalesAid Software
[email protected]
"markx" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Or put this immediately after the PasteSpecial line

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


Col

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" <[email protected]> wrote in message
news:[email protected]...
> try testing for length if you don't really have any one character text
> len(mycell)<2 or >1
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "markx" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

Thanks colofnature,
Your solution works perfectly!
Regards,
Mark


"colofnature" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>