Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
skipping empty cells that are not empty | Excel Programming | |||
Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) | |||
macro to colour empty cells (cells not recognized as empty) | Excel Programming | |||
Can blank cells created using empty Double-Quotes not be empty?? | Excel Programming |