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