Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 130
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 28
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need blank cells to remain empty Bebe Excel Worksheet Functions 7 March 25th 09 03:23 AM
Nested IF that will ignore blank (empty) cells Struggling in Sheffield[_2_] New Users to Excel 10 February 9th 09 08:40 PM
Exporting a worksheet with blank/empty cells jjk98 Excel Discussion (Misc queries) 2 May 22nd 07 01:12 PM
Excel - Autom. Filter "Empty / Non Empty cells" should come first Rom Excel Discussion (Misc queries) 0 August 10th 05 04:32 PM
How can I convert empty strings to empty cells? Shane Excel Discussion (Misc queries) 2 July 19th 05 12:10 PM


All times are GMT +1. The time now is 05:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"