EXCEL2003: non-empty empty cells

Topic

New Reply

The attached spreadsheet appears to be void of data in columns A:D, except for the first row, which contains column headings.
Yet, when I use Ctrl-Home to return to cell A1 and then Ctrl-DownArrow, the cursor travels to Cell A173. From A174, Ctrl-DownArrow takes me to row 65336 as I would expect.

Code: Select all

Sub test()
    MsgBox Len(ActiveCell.Value)
End Sub

The MsgBox suggests to me that the cell is empty, yet from within Excel2003 I can detect no evidence of cell contents.

For example, loading a space into the cell makes it appear to be empty, but Len(ActiveCell.Value) then reports 1 rather than 0.

The file originated in an Android phone as an export of contacts to a VCF file (“20210301.vcf.xls”).

In another kingdom not so far away from where I type, a suggestion is made that “The cells have an invisible attribute that makes Excel treat them as non-empty”.

This application is supposed to run unattended as a utility might, cleaning up, we hope, thousands of workbooks for hundreds of users in dozens of cities … and so we need a VBA-programmable solution to treating these non-empty empty cells.

Thanks for any suggestions.
Chris

Unless you're in a hurry, just wait.

Reply | Quote

Replies

Tip to anyone looking to respond:

This link above

Chris Greaves wrote:

Code: Select all

is a hidden notification of cross-posting, and a good deal of response has already occurred there.

Reply | Quote

I don’t have/use Access, so this may be a bit off-topic; but there is something to keep in mind when dealing with ‘the data-that-aren’t there’.

As a human user, we deal with ‘printable’ characters: letters, digits, ‘special-characters’. There also are many ‘un-printable’ ‘characters’, some are used for control purposes. Others are special-purpose substitutes in certain situations. The un-printable bit patterns do have meaning within their own application context, and can cause consternation when one is not aware of their existence.

The ‘usual’ culprit when looking at an empty cell is just the ‘space’ character:
in ASCII, decimal 32, hexadecimal ’20’.

But the most vague of all characters is the bit pattern ’00’, the null character: 0/’00’. It both exists (in a bit pattern, and takes up space), and doesn’t exist (as a visible/presentable entity).

(In ASCII there also exist a ‘substitute null’ (128/’80’), and a ‘substitute space’ 255/’FF’.)

So, when presented with an ’empty’ cell that isn’t empty, it may not be detectable with standard diagnostics (or shows up as length zero), but there really is ‘something’ there. Only an examination at the bit level will reveal its true nature.

In most cases a tap/s on the keyboard Delete key will remove this invisible ‘whatever-it-is-anyway’ annoyance. A complete cell selection may be needed, depending upon the cursor position.

I don’t know how this would have to be treated by an application; perhaps by the failure of a ‘binary-to-(printable)character’ translation? But are there valid binary-coded numbers?

Reply | Quote

I failed to note three things as general caveats:
1 – Don’t confuse bit patterns and ASCII codes with characters.
The character “0”(zero) is ASCII decimal 48, hex ’30’.
2 – ASCII is not the only set of bit-codings to represent characters. But it is (as I understand it) the most prevalent in computing.
3 – And, there is double-byte character encodings, which is needed to accommodate non-English / non-‘Latin’-character languages.

Reply | Quote