Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP using FALSE returning blanks and #N/A | Excel Worksheet Functions | |||
Sumproduct copying blanks or how to insert zero into blanks | Excel Worksheet Functions | |||
copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) | |||
True Or False, no matter what... it still displays the false statement | Excel Programming |