Blank Cell Oddity

[Zeke]

I have a spreadsheet that shows either a price or a discount (never has both)
based on given quantities. I have a formula that goes and looks at each cell
to determine which to use. I am using the IFBLANK function. If the price
cell is blank, it applies a discount to another number in the sheet. If the
price cell is not blank, it uses that price. The problem I have is that some
cells it considers to not be blank are blank. I can copy and paste a "blank"
cell to another cell and there is nothing in there. If I click a random cell
and type ="blank cell #", it is blank. Now, if I double click that cell and
hit enter (without entering anything), it then treats the cell as blank. I
have over 14,000 lines and about 8 rows of this data that I would like to
avoid having to double click each blank row to get this to work. Any
suggestions?

 

[Don Guillett]

I have found one way to do this is to check for the dreaded space bar. find
a cell and touch the spacebar to see what I mean. This will clear the cells
or use the check within your code
If Len(Application.Trim(Cells(i + 1, mc))) < 1

mc=3 'col c
for i=1 to cells(rows.count,mc).end(xlup).row
If Len(Application.Trim(Cells(i + 1, mc))) < 1 _
Then Cells(i + 1, mc) = ""
next i

 

[Shane Devenshire]

Hi,

You get different results depending on how you test, for example:
=ISBLANK(E12)
=COUNTBLANK(E12)
=IF(E12="","yes","no")

A cell with a formula that evaluates to "" is not considered blank by the
ISBLANK function, but is by the other two. A formula containing a spacebar
looks blank but none of these formulas will see it as blank. Then there is
the issue of hidden characters which may get into your spreadsheet when you
import data from an external source (or copy it).

Find and Replace can be used in many but not all cases to take care of the
problem. If the problem is spacebars then you can type a spacebar in the
Find what box of the Find & Replace dialog box and leave the Replace with box
empty, check Match entire cell contents under Options and Replace All.