When is an Empty Cell not Blank?

  • Thread starter Thread starter Cloudfall
  • Start date Start date
C

Cloudfall

I am using the Advanced Filter to filter out records of companies that
have not supplied a business number in that field (column). To my
astonishment, it isn't filtering them out! Furthermore, on
investigation, using "Edit, Go To..., Special, Blanks", these empty
fields were not selected, so Excel doesn't consider them blank! I can
make them blank by highlighting them and then deleting them, but that's
not the point. You can also copy and paste these things!
1. What are these cells? (Blank? Empty? Null? What?).
2. How do I search for them (so that I can then use VBA to find and
"delete" them).
3. These cells, whatever they are, would make a great party trick.
 
I found a fix for this problem in another group. The fix is:
Columns("E:E").Select [select the troublesome cells]
Selection.Value = Selection.Value [fix for the problem]
The cells do not contain zero-length strings (""). The formula bar
shows them as containing nothing.

I do not know why the cells behave the way they do, nor why this fix
works. I still think they would make a great party trick.
 
Hello

Even though you've found a fix for your problem, I think that the original
question is worthy of consideration and I wonder if anyone has an answer.
I, too, have come across situations where an apparantly blank cell isn;t
actually empty. Obvious examples would be if there's a formula (with error
trapping) which simply returns "" or if the font and text were set to the
same colour. I realise that each of these would show something as being
present in the formula bar.

I'm interested to know if there is a difference between "Null", "Empty",
"Blank" etc. and, if so, what the differences are.
 
There is no difference between isBlank and isEmpty, while the former is used
as a worksheet formula, the latter is used in the VBA.

As for null, it checks for null values for a variable. The help provides the
following on null:
Returns a Boolean value that indicates whether an expression contains no
valid data (Null).

E.g

Sub Test()
Dim a
a = Null
MsgBox IsNull(a)
End Sub

will print True. Remove the line
a = null
and then the above will return a false.


Mangesh





Tosca said:
Hello

Even though you've found a fix for your problem, I think that the original
question is worthy of consideration and I wonder if anyone has an answer.
I, too, have come across situations where an apparantly blank cell isn;t
actually empty. Obvious examples would be if there's a formula (with error
trapping) which simply returns "" or if the font and text were set to the
same colour. I realise that each of these would show something as being
present in the formula bar.

I'm interested to know if there is a difference between "Null", "Empty",
"Blank" etc. and, if so, what the differences are.


Cloudfall said:
I found a fix for this problem in another group. The fix is:
Columns("E:E").Select [select the troublesome cells]
Selection.Value = Selection.Value [fix for the problem]
The cells do not contain zero-length strings (""). The formula bar
shows them as containing nothing.

I do not know why the cells behave the way they do, nor why this fix
works. I still think they would make a great party trick.
 
Mr Peterson,

I want to clean up that detritus with your technique. I did it manually
(trying to record a macro) with the following results:

1. The computer ran out of memory and asked me if I wanted to skip
undo, which I OKed.
2. It then proceeded to take so long to fill the cells that I escaped
out of the process of putting $$$$$$ into each empty cell.

I think that both of the above happened because some of the time Excel
will work with a particular range of cells, and at other times it will
work with all cells in the spreadsheet. I assume it works with only a
certain number of cells when only a particular range (or ranges) is
selected.

I have to work with spreadsheets of raw data which do contain a fixed
number of columns (34), so I suppose I could lessen the problem
slightly by selected the range of columns from A to AH. But it would be
best to select only the rows with data in them as well. If Excel
doesn't have a command to do this, I suppose it could be done
programmatically.

I do have a column which must contain data (Australian Business Number
- ABN). I could count the number of rows with this number and then use
that along with known number of columns to select the range. The column
could contain this number as a number or string (or perhaps even
something else?) so I must count on the basis of at least either.
However, if the cells contain detritus down to row 65,536 I could wind
up with the slightly paradoxical situation of spending time to do a
count which won't help me when I have to do the $$$$$$ for ""
substitution, because I'll be doing it for 65,536 rows anyway.

So, is there a way of having Excel select the range automatically? Is
there some other more elegant solution to this problem (getting rid of
the detritus)?
 
The Edit|Replace stuff limits itself to the used range of that worksheet.

You could try resetting that used range by using techniques at Debra Dalgleish's
site:
http://www.contextures.com/xlfaqApp.html#Unused

(Before you do the edit|replace)

But it sounds like you're assigning the formulas (via code???) to all the rows
in the worksheet.

If that's the case, you may want to be more specific when you do add your
formulas.

Without knowing too much detail, you can pick out that ABN column and use that
to determine the last used row. Then use that row number when you put the
formulas in:

For instance:

dim LastRow as long
with worksheets("Sheet1")
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("B2:B" & lastrow).formula = "=yourformulahere"
end with

Then you've applied that formula just through the that last row.

And if that lastrow doesn't approach big numbers (whatever that means), the
clean up should be faster.
 
Back
Top