Empty cells not Empty!
 

Hi Guys,
I'm sure this is (another) microsoft glitch, so I'm looking for
work-around rather than a proper solution really...

I've got a sheet that runs a whole load of calculations through a se
of macros, which leaves me a with a sheet displaying a table with Vendo
on the left, an assesment number across the top, and a r,y or g (Re
Yellow or Green) in the table depending on what state they're in, for
particular assesment. The assesment that each vendor is at changes, an
obviously the Colour is variable as well.

My problem is this... I need to find the most recent assesment, an
record it's colour, but any COUNT function i try to run on the tabl
includes all the 'blank' cells as well. This affects any attempts t
do a selection.end(xlToRight) as well.
Manually, if I double click the 'blank' cell, and then 'de-select
it, it is now properly blank, and I can work with it fine. What'
going on, and how do I automate freeing up all the blank cells?!

I've tried multiplying everything by 1, paste special and changin
the format to text, everything is ineffectual!

Please help!

Many thanks,
Gareth.

P.S if anyone has Bill's personal Email, I'll be sure to let him kno
what I think of this little bug too

--
pianoma
-----------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...fo&userid=3371
View this thread: http://www.excelforum.com/showthread.php?threadid=54287

Empty cells not Empty!
 

From Dave Peterson:

Select the range
edit|replace
what: (leave blank)
with: $$$$$
replace all


followed by:
edit|replace
what: $$$$$
with: (leave blank)
replace all



--
Regards,
Tom Ogilvy


"pianoman" wrote:


Hi Guys,
I'm sure this is (another) microsoft glitch, so I'm looking for a
work-around rather than a proper solution really...

I've got a sheet that runs a whole load of calculations through a set
of macros, which leaves me a with a sheet displaying a table with Vendor
on the left, an assesment number across the top, and a r,y or g (Red
Yellow or Green) in the table depending on what state they're in, for a
particular assesment. The assesment that each vendor is at changes, and
obviously the Colour is variable as well.

My problem is this... I need to find the most recent assesment, and
record it's colour, but any COUNT function i try to run on the table
includes all the 'blank' cells as well. This affects any attempts to
do a selection.end(xlToRight) as well.
Manually, if I double click the 'blank' cell, and then 'de-select'
it, it is now properly blank, and I can work with it fine. What's
going on, and how do I automate freeing up all the blank cells?!

I've tried multiplying everything by 1, paste special and changing
the format to text, everything is ineffectual!

Please help!

Many thanks,
Gareth.

P.S if anyone has Bill's personal Email, I'll be sure to let him know
what I think of this little bug too!


--
pianoman
------------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712
View this thread: http://www.excelforum.com/showthread...hreadid=542870

Empty cells not Empty!
 

Spot on!
That will do very nicely thank you
Cheers Dave

Tom Ogilvy Wrote:
From Dave Peterson:

Select the range
edit|replace
what: (leave blank)
with: $$$$$
replace all


followed by:
edit|replace
what: $$$$$
with: (leave blank)
replace all



--
Regards,
Tom Ogilvy


"pianoman" wrote:


Hi Guys,
I'm sure this is (another) microsoft glitch, so I'm looking for a
work-around rather than a proper solution really...

I've got a sheet that runs a whole load of calculations through a
set
of macros, which leaves me a with a sheet displaying a table with
Vendor
on the left, an assesment number across the top, and a r,y or g (Red
Yellow or Green) in the table depending on what state they're in, for
a
particular assesment. The assesment that each vendor is at changes,
and
obviously the Colour is variable as well.

My problem is this... I need to find the most recent assesment, and
record it's colour, but any COUNT function i try to run on the table
includes all the 'blank' cells as well. This affects any attempts
to
do a selection.end(xlToRight) as well.
Manually, if I double click the 'blank' cell, and then 'de-select'
it, it is now properly blank, and I can work with it fine. What's
going on, and how do I automate freeing up all the blank cells?!

I've tried multiplying everything by 1, paste special and changing
the format to text, everything is ineffectual!

Please help!

Many thanks,
Gareth.

P.S if anyone has Bill's personal Email, I'll be sure to let him
know
what I think of this little bug too!


--
pianoman

------------------------------------------------------------------------
pianoman's Profile:
http://www.excelforum.com/member.php...o&userid=33712
View this thread:
http://www.excelforum.com/showthread...hreadid=542870




--
pianoman
------------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712
View this thread: http://www.excelforum.com/showthread...hreadid=542870