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.


-----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



.

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

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