Excel goes bananas while dealing with blanks

[a7n9]

Hello all,

Since quite a few days, I'm (and my office) having strange problems dealing with blanks.

1. I had a few numbers copy and pasted (special) from some formula in a column and in between it had blanks. I checked the lengths of the cells and they were zero, so they were blanks. However, when I tried to use Ctrl + Down key, it was jumping very weird, somehow it was just ignoring the blanks. Then I clicked inside a blank cell and then came out of it, tried Ctrl +Down again, it stopped for that cell but ignored other blanks.

2. I had some other numbers and blanks in a column, I used CountA to count the number of non-blank cells, again the same problem. It was counting blank cells too, but when I clicked inside a cell the count was reducing and it wasn't working for all the blank cells. Some blank cells even after clicking inside were still counted.

Sorry for such a long description but I had to explain. So has anybody faced similar kind of problem ever?

[Smitty]

Just a thought, but highlight that column, then Data-->Text to Columns-->Filter in place (destination should be the same as the source range).

HTH,

Smitty

[Mark W.]

...I checked the lengths of the cells and they were zero, so they were blanks...

You need to rethink the statement above.

******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A2B2C2 =
	A	B	C	D	E
1		LEN	ISBLANK
2		0	FALSE
3
4
5
Sheet4

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

[gaynard_nelson]

Originally Posted by a7n9

Hello all,

Since quite a few days, I'm (and my office) having strange problems dealing with blanks.

1. I had a few numbers copy and pasted (special) from some formula in a column and in between it had blanks. I checked the lengths of the cells and they were zero, so they were blanks. However, when I tried to use Ctrl + Down key, it was jumping very weird, somehow it was just ignoring the blanks. Then I clicked inside a blank cell and then came out of it, tried Ctrl +Down again, it stopped for that cell but ignored other blanks.

2. I had some other numbers and blanks in a column, I used CountA to count the number of non-blank cells, again the same problem. It was counting blank cells too, but when I clicked inside a cell the count was reducing and it wasn't working for all the blank cells. Some blank cells even after clicking inside were still counted.

Sorry for such a long description but I had to explain. So has anybody faced similar kind of problem ever?

Is it possible that you have a label in those supposedly blenk cells ('). If you do that could be the problem. That would show 0 length but is still occupied and end down arrow would consider it an occupied cell. The label would only be visible on the edit line and could be removed with the delete key.

[a7n9]

gaynard_nelson: No I checked it very closely, there was nothing in "blank cells"

Mark W.: Thanks, I wasn't aware of that. But why even after you copy and paste special it-you don't see anything there but still ISBLANK is False?

Smitty: Thanks, I'll try that.

Thanks all for your replies. I know what the problem is now, but what is solution how do we overcome it, if we've many rows. May be we can write macro, let me see.

[Mark W.]

Originally Posted by a7n9

gaynard_nelson: No I checked it very closely, there was nothing in "blank cells"

Mark W.: Thanks, I wasn't aware of that. But why even after you copy and paste special it-you don't see anything there but still ISBLANK is False?

Smitty: Thanks, I'll try that.

Because the null string, "", is something that can be copied and pasted. It has a LEN of 0, but ISTEXT returns TRUE, and ISBLANK returns FALSE.

Now to really mess you up... COUNTBLANK returns 1. Consult the Excel Help topic for these functions to understand these distinctions.

[a7n9]

Thanks for reply Mark.

I wrote this macro to create true blanks.

Sub make_true_blanks()
Dim c As Range
For Each c In Selection
    If Len(c) = 0 And WorksheetFunction.IsText(c) Then
        c.ClearContents
    End If
Next
MsgBox "Done!"
End Sub

[Mark W.]

Also, you could have selected any single, column verticle array, and used Excel's Data | Text to Columns... menu command to parse the values eliminating any empty (null) text strings, leading/trailing spaces, and for numeric values -- leading zeroes.