Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell is not blank
Excel XP & Win XP
This one is costing me hair. I am helping an OP from England. I am working with her file. I narrowed down my problem so I can explain it simply and without getting into VBA. I have a string of cells in a row that appear to be blank. I select a cell to the left of my problem cell and do End - Right arrow. Excel stops at the problem cell, that appears to be blank. I do =Len(That cell) and get zero. I do =CountA(That cell) and get 1. All the cells have Conditional Formatting as a function of row number with a format of color. Most of the "empty" cells act like empty cells. But a few, like my problem cell, do not. The code "If ActiveCell.Value="" Then..." says it's True. The code "If IsEmpty(ActiveCell.Value) Then... says its False. I can do Edit - Clear - Contents and the problem is solved. I know I can run a code that loops through all the cells in the used range, like: If i.Value="" Then i.ClearContents and solve this problem, but my question is: What is happening with that cell? Thanks for your time. Otto |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell is not blank
Hi Otto,
I suspect the following: The cell is not empty in the sense of IsEmpty, because there is a formula in it The cell is "" because the result of the formula is "" -- Kind regards, Niek Otten Microsoft MVP - Excel "Otto Moehrbach" wrote in message ... | Excel XP & Win XP | This one is costing me hair. | I am helping an OP from England. | I am working with her file. | I narrowed down my problem so I can explain it simply and without getting | into VBA. | I have a string of cells in a row that appear to be blank. | I select a cell to the left of my problem cell and do End - Right arrow. | Excel stops at the problem cell, that appears to be blank. | I do =Len(That cell) and get zero. | I do =CountA(That cell) and get 1. | All the cells have Conditional Formatting as a function of row number with a | format of color. Most of the "empty" cells act like empty cells. But a | few, like my problem cell, do not. | The code "If ActiveCell.Value="" Then..." says it's True. | The code "If IsEmpty(ActiveCell.Value) Then... says its False. | I can do Edit - Clear - Contents and the problem is solved. | I know I can run a code that loops through all the cells in the used range, | like: | If i.Value="" Then i.ClearContents | and solve this problem, but my question is: What is happening with that | cell? Thanks for your time. Otto | | |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell is not blank
If the =len() formula returns a 0, then my bet is that you used to have a
formula in those "blank" cells. They evaluated to "". like: =if(a1=3,"",a1+7) Then you did a copy|paste special|values. This leaves the cell looking blank, but it's not. (Try =isblank() against one of the offending cells.) And you can see the "detritus" left behind by toggling a setting. Tools|options|transition tab|check transition navigation keys. You'll see an apostrophe in the formula bar with that cell selected. === I like this way to clean up that type of "blank" cell. select the range (or the whole sheet) edit|replace what: (leave blank) with: $$$$$ (some unique value--not used!) Replace all Then reverse it: edit|replace what: $$$$$ (that same value) with: (leave blank) Replace all == If you're using VBA, you could record a macro when you do those two Edit|Replace and plop that into your code to clean up that junk. Otto Moehrbach wrote: Excel XP & Win XP This one is costing me hair. I am helping an OP from England. I am working with her file. I narrowed down my problem so I can explain it simply and without getting into VBA. I have a string of cells in a row that appear to be blank. I select a cell to the left of my problem cell and do End - Right arrow. Excel stops at the problem cell, that appears to be blank. I do =Len(That cell) and get zero. I do =CountA(That cell) and get 1. All the cells have Conditional Formatting as a function of row number with a format of color. Most of the "empty" cells act like empty cells. But a few, like my problem cell, do not. The code "If ActiveCell.Value="" Then..." says it's True. The code "If IsEmpty(ActiveCell.Value) Then... says its False. I can do Edit - Clear - Contents and the problem is solved. I know I can run a code that loops through all the cells in the used range, like: If i.Value="" Then i.ClearContents and solve this problem, but my question is: What is happening with that cell? Thanks for your time. Otto -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell is not blank
Thanks Niek, but there are no formulas in those problem cells.
"Niek Otten" wrote in message ... Hi Otto, I suspect the following: The cell is not empty in the sense of IsEmpty, because there is a formula in it The cell is "" because the result of the formula is "" -- Kind regards, Niek Otten Microsoft MVP - Excel "Otto Moehrbach" wrote in message ... | Excel XP & Win XP | This one is costing me hair. | I am helping an OP from England. | I am working with her file. | I narrowed down my problem so I can explain it simply and without getting | into VBA. | I have a string of cells in a row that appear to be blank. | I select a cell to the left of my problem cell and do End - Right arrow. | Excel stops at the problem cell, that appears to be blank. | I do =Len(That cell) and get zero. | I do =CountA(That cell) and get 1. | All the cells have Conditional Formatting as a function of row number with a | format of color. Most of the "empty" cells act like empty cells. But a | few, like my problem cell, do not. | The code "If ActiveCell.Value="" Then..." says it's True. | The code "If IsEmpty(ActiveCell.Value) Then... says its False. | I can do Edit - Clear - Contents and the problem is solved. | I know I can run a code that loops through all the cells in the used range, | like: | If i.Value="" Then i.ClearContents | and solve this problem, but my question is: What is happening with that | cell? Thanks for your time. Otto | | |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell is not blank
It appears that you hit it on the head. The cell contains a carrot (Shift -
6). Perhaps that's a British version thing. I ran a loop with the code: If i.Value="" Then i.ClearContents through the used range and solved the problem. Thanks. Otto "Dave Peterson" wrote in message ... If the =len() formula returns a 0, then my bet is that you used to have a formula in those "blank" cells. They evaluated to "". like: =if(a1=3,"",a1+7) Then you did a copy|paste special|values. This leaves the cell looking blank, but it's not. (Try =isblank() against one of the offending cells.) And you can see the "detritus" left behind by toggling a setting. Tools|options|transition tab|check transition navigation keys. You'll see an apostrophe in the formula bar with that cell selected. === I like this way to clean up that type of "blank" cell. select the range (or the whole sheet) edit|replace what: (leave blank) with: $$$$$ (some unique value--not used!) Replace all Then reverse it: edit|replace what: $$$$$ (that same value) with: (leave blank) Replace all == If you're using VBA, you could record a macro when you do those two Edit|Replace and plop that into your code to clean up that junk. Otto Moehrbach wrote: Excel XP & Win XP This one is costing me hair. I am helping an OP from England. I am working with her file. I narrowed down my problem so I can explain it simply and without getting into VBA. I have a string of cells in a row that appear to be blank. I select a cell to the left of my problem cell and do End - Right arrow. Excel stops at the problem cell, that appears to be blank. I do =Len(That cell) and get zero. I do =CountA(That cell) and get 1. All the cells have Conditional Formatting as a function of row number with a format of color. Most of the "empty" cells act like empty cells. But a few, like my problem cell, do not. The code "If ActiveCell.Value="" Then..." says it's True. The code "If IsEmpty(ActiveCell.Value) Then... says its False. I can do Edit - Clear - Contents and the problem is solved. I know I can run a code that loops through all the cells in the used range, like: If i.Value="" Then i.ClearContents and solve this problem, but my question is: What is happening with that cell? Thanks for your time. Otto -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell is not blank
There were some old Lotus shortcuts that excel will use (with that setting
toggled): ^asdf (will center asdf in the cell) "asdf (will right justify asdf in the cell) Otto Moehrbach wrote: It appears that you hit it on the head. The cell contains a carrot (Shift - 6). Perhaps that's a British version thing. I ran a loop with the code: If i.Value="" Then i.ClearContents through the used range and solved the problem. Thanks. Otto "Dave Peterson" wrote in message ... If the =len() formula returns a 0, then my bet is that you used to have a formula in those "blank" cells. They evaluated to "". like: =if(a1=3,"",a1+7) Then you did a copy|paste special|values. This leaves the cell looking blank, but it's not. (Try =isblank() against one of the offending cells.) And you can see the "detritus" left behind by toggling a setting. Tools|options|transition tab|check transition navigation keys. You'll see an apostrophe in the formula bar with that cell selected. === I like this way to clean up that type of "blank" cell. select the range (or the whole sheet) edit|replace what: (leave blank) with: $$$$$ (some unique value--not used!) Replace all Then reverse it: edit|replace what: $$$$$ (that same value) with: (leave blank) Replace all == If you're using VBA, you could record a macro when you do those two Edit|Replace and plop that into your code to clean up that junk. Otto Moehrbach wrote: Excel XP & Win XP This one is costing me hair. I am helping an OP from England. I am working with her file. I narrowed down my problem so I can explain it simply and without getting into VBA. I have a string of cells in a row that appear to be blank. I select a cell to the left of my problem cell and do End - Right arrow. Excel stops at the problem cell, that appears to be blank. I do =Len(That cell) and get zero. I do =CountA(That cell) and get 1. All the cells have Conditional Formatting as a function of row number with a format of color. Most of the "empty" cells act like empty cells. But a few, like my problem cell, do not. The code "If ActiveCell.Value="" Then..." says it's True. The code "If IsEmpty(ActiveCell.Value) Then... says its False. I can do Edit - Clear - Contents and the problem is solved. I know I can run a code that loops through all the cells in the used range, like: If i.Value="" Then i.ClearContents and solve this problem, but my question is: What is happening with that cell? Thanks for your time. Otto -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Blank cell is not blank
Thanks Dave. Otto
"Dave Peterson" wrote in message ... There were some old Lotus shortcuts that excel will use (with that setting toggled): ^asdf (will center asdf in the cell) "asdf (will right justify asdf in the cell) Otto Moehrbach wrote: It appears that you hit it on the head. The cell contains a carrot (Shift - 6). Perhaps that's a British version thing. I ran a loop with the code: If i.Value="" Then i.ClearContents through the used range and solved the problem. Thanks. Otto "Dave Peterson" wrote in message ... If the =len() formula returns a 0, then my bet is that you used to have a formula in those "blank" cells. They evaluated to "". like: =if(a1=3,"",a1+7) Then you did a copy|paste special|values. This leaves the cell looking blank, but it's not. (Try =isblank() against one of the offending cells.) And you can see the "detritus" left behind by toggling a setting. Tools|options|transition tab|check transition navigation keys. You'll see an apostrophe in the formula bar with that cell selected. === I like this way to clean up that type of "blank" cell. select the range (or the whole sheet) edit|replace what: (leave blank) with: $$$$$ (some unique value--not used!) Replace all Then reverse it: edit|replace what: $$$$$ (that same value) with: (leave blank) Replace all == If you're using VBA, you could record a macro when you do those two Edit|Replace and plop that into your code to clean up that junk. Otto Moehrbach wrote: Excel XP & Win XP This one is costing me hair. I am helping an OP from England. I am working with her file. I narrowed down my problem so I can explain it simply and without getting into VBA. I have a string of cells in a row that appear to be blank. I select a cell to the left of my problem cell and do End - Right arrow. Excel stops at the problem cell, that appears to be blank. I do =Len(That cell) and get zero. I do =CountA(That cell) and get 1. All the cells have Conditional Formatting as a function of row number with a format of color. Most of the "empty" cells act like empty cells. But a few, like my problem cell, do not. The code "If ActiveCell.Value="" Then..." says it's True. The code "If IsEmpty(ActiveCell.Value) Then... says its False. I can do Edit - Clear - Contents and the problem is solved. I know I can run a code that loops through all the cells in the used range, like: If i.Value="" Then i.ClearContents and solve this problem, but my question is: What is happening with that cell? Thanks for your time. Otto -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula returns blank in the cell where it is entered | Excel Worksheet Functions | |||
Setting Purely BLANK Cell | Excel Worksheet Functions | |||
Replace null string with blank cell | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Look for change next blank cell in Range | Excel Worksheet Functions |