Removing "dummy" cell entries - zero length

[Tim Childs]

Hi

I have done some searching of the archive but not successfully for the
following problem.

I have datafiles produced from an accounting ledger system and <null> cells
are produced for columns that have no data. The null cells are zero length
strings that show up as text items i.e. ISTEXT(A1) is TRUE, but equally
LEN(A1) is 0 and ISBLANK(A1) is FALSE. When in Excel itself, simply editing
the cell makes it revert to a true empty (blank) cell.

What is the most efficient way of identifying the cells in VBA and then
removing them, please? (Ideally, I don't want to test each cell). The cells
can be anywhere in the sheet data but are often in whole columns.

Thanks

Tim

 

[Tim Childs]

Hi

many thanks - will try it out

bw

Tim

 

[Dave Peterson]

I like this way to clean up that type of "blank" cell.

select the range (or the whole sheet)
edit|replace
what: (leave blank)
with: $$$$$ (some unique value--not used!)
Replace all

Then reverse it:
edit|replace
what: $$$$$ (that same value)
with: (leave blank)
Replace all

========
You can also get this detritus(?) when you have a formula that evaluates to ""
and is converted to values.

=if(a1>7,"ok","")

 

[Tim Childs]

Hi Dave

Many thanks for this - I will try this out

Looks very neat and, I suspect, very fast which is important

u've not lost your edge!!!

THANKS

Tim