Blank cells corrupt?

[Dave Unger]

Hello,

I received a simple spreadsheet from a client, mainly consisting of a
column of numbers. The numbers are arranged in groups of 10, each
group is separated by a blank cell.

Here’s the problem – if I try to select those blank cells, either by
Goto>Special>Blanks from the menu, or r.SpecialCells
(xlCellTypeBlanks).Select via VBA, I get a “No cells were found” error
message. And yet r.Find("").Select works just fine.

There are no formulas in these cells, or anything else out of the
ordinary that I can see. If I click in the formula bar & hit Enter,
everything works as expected. Also, stepping thru the blank cells via
VBA and making a change also fixes it.

Can someone suggest to me what may be the problem, and how those cells
could have got into that state in the first place? Thank you.

Regards,

DaveU

 

[Gary''s Student]

Your problem is the "blank" cells are not genuine empties. These cells are
easy to create for demonstration purposes:

1. pick a cell, say A1 and clrear it with Edit > Clear > all
2. then enter a null with =""
3. then copy A1 and paste/special/values back onto A1

At this point, A1 APPEARS to be empty, both in the cell and in the formula
bar.
However, =ISBLANK(A1) returns FALSE!!

This type of "pseudo-blank" cell will not be found by goto special blanks.

You can also create "pseudo-blanks" with VBA:

Sub demo()
Set a1 = Range("A1")
a1.NumberFormat = "@"
a1.Value = ""
End Sub

 

[Dave Peterson]

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

In code you could do something like:

Option Explicit
Sub testme()
With ActiveSheet
With .cells 'or a specific range: With .Range("D")
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End With
End Sub

 

[Dave Unger]

Hello Gary”s Student & Dave

Gary”s Student, thanks for the excellent explanation – yes, that’s
exactly how the client built the sheet, using copy/pastespecial/values
methods.

Dave, thanks for the Toggle Transition hint – I’ve never used this
before, but I see it can be a useful tool. Also appreciate the
cleanup suggestions.

While I don’t pretend to be an expert, I have been coding in VBA for a
few years, but have never encountered this before. It quite took me
by surprise, as I thought I had “mastered” this area. One never stops
learning, do they?

Again, thanks so much to both of you, very much appreciated.
Regards,

DaveU