Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can blank cells created using empty Double-Quotes not be empty??
Hello there,
I have a spreadsheet into which I load daily data using a macro. There is data for about 50% of the codes each day. First, I test whether there are any new codes, and add those to the bottom of my list. Then I load the data using the VLOOKUP simplified as follows- = if(ISERROR(<VLOOKUP formula),"",(<VLOOKUP formula) NOTE:- There is no space between the two double-quotes. Then - "Copy" - "Paste Special-Values" - to remove the formulas. Then I was playing around and noticed that- -"Edit" -"Go To..." -"Special" -"Blanks" only selected the earlier empty cells for new codes added, but not for old codes with empty cells. I changed the macro to ".ClearContents" for all empty cells created by the VLOOKUP formula above. The result was that I was able to select all blanks using the "Go To..."-etc- "Blanks" command shown above. More astonishingly my file size reduced from 8 megabytes to 4 megabytes. This is the reason for my question - "Can blank cells created using empty Double-Quotes not be empty?" regards, JohnI |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can blank cells created using empty Double-Quotes not be empty??
Hi John,
I think the change in size is simply a reflection of the fact that putting double quotes in a cell changes them from being empty to having something - usually taken to be a nul value. Clearing the contents subsequently cleared out those nul values, and 'nothing' takes less space than a nul to store. Cheers PS: Remove NO.SPAM from the above before replying. -----Original Message----- Hello there, I have a spreadsheet into which I load daily data using a macro. There is data for about 50% of the codes each day. First, I test whether there are any new codes, and add those to the bottom of my list. Then I load the data using the VLOOKUP simplified as follows- = if(ISERROR(<VLOOKUP formula),"",(<VLOOKUP formula) NOTE:- There is no space between the two double-quotes. Then - "Copy" - "Paste Special-Values" - to remove the formulas. Then I was playing around and noticed that- -"Edit" -"Go To..." -"Special" -"Blanks" only selected the earlier empty cells for new codes added, but not for old codes with empty cells. I changed the macro to ".ClearContents" for all empty cells created by the VLOOKUP formula above. The result was that I was able to select all blanks using the "Go To..."-etc- "Blanks" command shown above. More astonishingly my file size reduced from 8 megabytes to 4 megabytes. This is the reason for my question - "Can blank cells created using empty Double-Quotes not be empty?" regards, JohnI . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can blank cells created using empty Double-Quotes not be empty??
Try converting one of those formulas that evaluate to "" to values once more.
Now do this. Turn on Tools|Options|Transition Tab|Transition Navigation Keys Select that cell and look in the formula bar. It isn't empty. You'll see some detritus (my term) left behind. Now try selecting a few of those cells and do Edit|Replace Find what: (leave blank) Replace with: $$$$$ (some unique set of characters) Replace all Tnen do the opposite Edit|Replace find what: $$$$$ (same unique characters) replace with: (leave blank) Replace all You could add code that does the same kind of thing. Another way to get rid of those single quotes is: With Worksheets("sheet1").Range("a1:a9999") .Value = .Value End With The only difference I've seen in these two approaches is when you have a cell that has a mixed format (some characters bold, some not. Some red, some not). The .value loses that character by character formatting. Don't forget to turn off that transition navigation keys stuff. JohnI in Brisbane wrote: Hello there, I have a spreadsheet into which I load daily data using a macro. There is data for about 50% of the codes each day. First, I test whether there are any new codes, and add those to the bottom of my list. Then I load the data using the VLOOKUP simplified as follows- = if(ISERROR(<VLOOKUP formula),"",(<VLOOKUP formula) NOTE:- There is no space between the two double-quotes. Then - "Copy" - "Paste Special-Values" - to remove the formulas. Then I was playing around and noticed that- -"Edit" -"Go To..." -"Special" -"Blanks" only selected the earlier empty cells for new codes added, but not for old codes with empty cells. I changed the macro to ".ClearContents" for all empty cells created by the VLOOKUP formula above. The result was that I was able to select all blanks using the "Go To..."-etc- "Blanks" command shown above. More astonishingly my file size reduced from 8 megabytes to 4 megabytes. This is the reason for my question - "Can blank cells created using empty Double-Quotes not be empty?" regards, JohnI -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can blank cells created using empty Double-Quotes not be empty??
Another way might be to just use a straight/single Vlookup, so the
doublequote cells would have a #N/A instead. Either before doing the pastespecial or after, do Edit=Goto=special, select either formulas or constants (depending on when you do it) and Errors. then do Delete or Edit=Clearcontents. -- Regards, Tom Ogilvy JohnI in Brisbane wrote in message ... macropod & Dave, Thanks to both of you for replying. I tried out what you said below, Dave, & found it interesting that a "single quote" character became visible in the cell after changing the Navigation Key option. This is like the option I sometimes use to force a cells contents to be text, by entering a single-quote as the first character. The difference is that entering a single-quote into a cell shows in the formula bar even when the Navigation Key option is turned off. Thanks too for the shortcut way to remove the "detritus". :-) regards, JohnI "Dave Peterson" wrote in message ... Try converting one of those formulas that evaluate to "" to values once more. Now do this. Turn on Tools|Options|Transition Tab|Transition Navigation Keys Select that cell and look in the formula bar. It isn't empty. You'll see some detritus (my term) left behind. Now try selecting a few of those cells and do Edit|Replace Find what: (leave blank) Replace with: $$$$$ (some unique set of characters) Replace all Tnen do the opposite Edit|Replace find what: $$$$$ (same unique characters) replace with: (leave blank) Replace all You could add code that does the same kind of thing. Another way to get rid of those single quotes is: With Worksheets("sheet1").Range("a1:a9999") .Value = .Value End With The only difference I've seen in these two approaches is when you have a cell that has a mixed format (some characters bold, some not. Some red, some not). The .value loses that character by character formatting. Don't forget to turn off that transition navigation keys stuff. JohnI in Brisbane wrote: Hello there, I have a spreadsheet into which I load daily data using a macro. There is data for about 50% of the codes each day. First, I test whether there are any new codes, and add those to the bottom of my list. Then I load the data using the VLOOKUP simplified as follows- = if(ISERROR(<VLOOKUP formula),"",(<VLOOKUP formula) NOTE:- There is no space between the two double-quotes. Then - "Copy" - "Paste Special-Values" - to remove the formulas. Then I was playing around and noticed that- -"Edit" -"Go To..." -"Special" -"Blanks" only selected the earlier empty cells for new codes added, but not for old codes with empty cells. I changed the macro to ".ClearContents" for all empty cells created by the VLOOKUP formula above. The result was that I was able to select all blanks using the "Go To..."-etc- "Blanks" command shown above. More astonishingly my file size reduced from 8 megabytes to 4 megabytes. This is the reason for my question - "Can blank cells created using empty Double-Quotes not be empty?" regards, JohnI -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can blank cells created using empty Double-Quotes not be empty??
When I know that I'm going to convert the formulas to values, I'll just use a
single function call like Tom describes. Then I convert to values and edit|replace #n/a with nothing (leave it blank). It's usually quicker (only one function call in the formula) and only one Edit|Replace (by hand or via a macro.) JohnI in Brisbane wrote: macropod & Dave, Thanks to both of you for replying. I tried out what you said below, Dave, & found it interesting that a "single quote" character became visible in the cell after changing the Navigation Key option. This is like the option I sometimes use to force a cells contents to be text, by entering a single-quote as the first character. The difference is that entering a single-quote into a cell shows in the formula bar even when the Navigation Key option is turned off. Thanks too for the shortcut way to remove the "detritus". :-) regards, JohnI "Dave Peterson" wrote in message ... Try converting one of those formulas that evaluate to "" to values once more. Now do this. Turn on Tools|Options|Transition Tab|Transition Navigation Keys Select that cell and look in the formula bar. It isn't empty. You'll see some detritus (my term) left behind. Now try selecting a few of those cells and do Edit|Replace Find what: (leave blank) Replace with: $$$$$ (some unique set of characters) Replace all Tnen do the opposite Edit|Replace find what: $$$$$ (same unique characters) replace with: (leave blank) Replace all You could add code that does the same kind of thing. Another way to get rid of those single quotes is: With Worksheets("sheet1").Range("a1:a9999") .Value = .Value End With The only difference I've seen in these two approaches is when you have a cell that has a mixed format (some characters bold, some not. Some red, some not). The .value loses that character by character formatting. Don't forget to turn off that transition navigation keys stuff. JohnI in Brisbane wrote: Hello there, I have a spreadsheet into which I load daily data using a macro. There is data for about 50% of the codes each day. First, I test whether there are any new codes, and add those to the bottom of my list. Then I load the data using the VLOOKUP simplified as follows- = if(ISERROR(<VLOOKUP formula),"",(<VLOOKUP formula) NOTE:- There is no space between the two double-quotes. Then - "Copy" - "Paste Special-Values" - to remove the formulas. Then I was playing around and noticed that- -"Edit" -"Go To..." -"Special" -"Blanks" only selected the earlier empty cells for new codes added, but not for old codes with empty cells. I changed the macro to ".ClearContents" for all empty cells created by the VLOOKUP formula above. The result was that I was able to select all blanks using the "Go To..."-etc- "Blanks" command shown above. More astonishingly my file size reduced from 8 megabytes to 4 megabytes. This is the reason for my question - "Can blank cells created using empty Double-Quotes not be empty?" regards, JohnI -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can blank cells created using empty Double-Quotes not be empty??
Perhaps this is seen more clearly in the VBA analog. Again with A1:A7
filled with 3 empty strings and 4 blanks, execute Set rng = Range("A1:A7") For i = 1 To 7 If rng(i).Value = "" Then k = k + 1 Next MsgBox k '<------Displays 7 For i = 1 To 7 If IsEmpty(rng(i)) Then k = k + 1 Next MsgBox k '<------Displays 4 Alan Beban Alan Beban wrote: For a general response, cells that contain the empty string (i.e., cells into which has been entered ="") are certainly distinguishable from cells with nothing in them i.e., blanks. One way to observe this is to enter the empty string into 3 of the cells in A1:A7, leaving the other 4 cells blank. Then =COUNTIF(A1:A7,"=") will return 4, the number of blanks, and =COUNTIF(A1:A7,"") will return 7, the combined count. So, obviously, =COUNTIF(A1:A7,"")-COUNTIF(A1:A7,"=") will return 3, the number of empty strings. Alan Beban JohnI in Brisbane wrote: Hello there, I have a spreadsheet into which I load daily data using a macro. There is data for about 50% of the codes each day. First, I test whether there are any new codes, and add those to the bottom of my list. Then I load the data using the VLOOKUP simplified as follows- = if(ISERROR(<VLOOKUP formula),"",(<VLOOKUP formula) NOTE:- There is no space between the two double-quotes. Then - "Copy" - "Paste Special-Values" - to remove the formulas. Then I was playing around and noticed that- -"Edit" -"Go To..." -"Special" -"Blanks" only selected the earlier empty cells for new codes added, but not for old codes with empty cells. I changed the macro to ".ClearContents" for all empty cells created by the VLOOKUP formula above. The result was that I was able to select all blanks using the "Go To..."-etc- "Blanks" command shown above. More astonishingly my file size reduced from 8 megabytes to 4 megabytes. This is the reason for my question - "Can blank cells created using empty Double-Quotes not be empty?" regards, JohnI |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can blank cells created using empty Double-Quotes not be empty??
Tom, Alan & Dave,
Thanks for your responses to my message. I've learnt a lot from them. I'll read your posts a few times more to make sure I understand them as much as I can. There's lots to learn about Excel. :-) regards, JohnI "JohnI in Brisbane" wrote in message ... Hello there, I have a spreadsheet into which I load daily data using a macro. There is data for about 50% of the codes each day. First, I test whether there are any new codes, and add those to the bottom of my list. Then I load the data using the VLOOKUP simplified as follows- = if(ISERROR(<VLOOKUP formula),"",(<VLOOKUP formula) NOTE:- There is no space between the two double-quotes. Then - "Copy" - "Paste Special-Values" - to remove the formulas. Then I was playing around and noticed that- -"Edit" -"Go To..." -"Special" -"Blanks" only selected the earlier empty cells for new codes added, but not for old codes with empty cells. I changed the macro to ".ClearContents" for all empty cells created by the VLOOKUP formula above. The result was that I was able to select all blanks using the "Go To..."-etc- "Blanks" command shown above. More astonishingly my file size reduced from 8 megabytes to 4 megabytes. This is the reason for my question - "Can blank cells created using empty Double-Quotes not be empty?" regards, JohnI |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need blank cells to remain empty | Excel Worksheet Functions | |||
Nested IF that will ignore blank (empty) cells | New Users to Excel | |||
Exporting a worksheet with blank/empty cells | Excel Discussion (Misc queries) | |||
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) |