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