cntrl + down arrow and null values in cells

[Craig]

Cntrl+ down arrow is taking me to the next blank cell. The problem is that many
cells in a column appear blank, but they actually have something in them.
These data are being copied from other sheets, and there are no blank spaces
in the cells. I've tried using paste special - values, but this also does not
work.

So my question: What could be in these cells that stops excel from seeing
them as blank? A colleague is also having this happen to him with data
exported from Access. Maybe excel sees a null value or something and
considers that different from blank?

I'm dealing with very large spreadsheets, and I want to be able to quickly
verify if there is data in all columns. I want to use cntrl plus arrow down
for this, but I can't as long as excel views these null cells as containing
values.
thank you,
craig

 

[Dave Peterson]

If you have a formula that evaluates to "" (like =if(a1>0,"error","")) and it's
converted to values then this cell isn't really empty!

You can see this by (temporarily) toggling this setting:

Tools|options|transition tab|check Transition navigation keys.
You'll see an apostrophe in the formula bar.
(toggle that setting back!)



One way of cleaning up this detritus:

Select the range to fix
edit|replace
what: (leave blank)
with: $$$$$ (my unique string)
replace all

then one more time
edit|replace
what: $$$$$
with: (leave blank)
replace all

 

[PeterAtherton]

If you have a formula that evaluates to "" (like =if(a1>0,"error","")) and it's
converted to values then this cell isn't really empty!

You can see this by (temporarily) toggling this setting:

Tools|options|transition tab|check Transition navigation keys.
You'll see an apostrophe in the formula bar.
(toggle that setting back!)



One way of cleaning up this detritus:

Select the range to fix
edit|replace
what: (leave blank)
with: $$$$$ (my unique string)
replace all

then one more time
edit|replace
what: $$$$$
with: (leave blank)
replace all

Dave Peterson

Assuming that there are no formulas you might have non printing characters.
If Daves method is too involved you could try this macro.

Sub clearNonBlanks()
Dim c
For Each c In Selection
'get rid of blanks
c.Value = Trim(c)
If IsDate(c) Then
c = c
End If
'ensure date values are correct
If IsNumeric(c) And _
Not IsDate(c) Then
c = c * 1
End If
If Asc(c) < 32 Then
c.Delete
End If
Next
End Sub

Copy sub into a VB Module, (ALT + F8, INsert Module) Select the all the
cells in the sheet and run the macro

Regards
Peter