Can blank cells created using empty Double-Quotes not be empty??

  • Thread starter Thread starter JohnI in Brisbane
  • Start date Start date
J

JohnI in Brisbane

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
 
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.
 
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.
 
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 said:
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 said:
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
Click to expand...
see
Click to expand...
 
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.)
 
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
 
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
 
Back
Top