Removing "dummy" cell entries - zero length
 

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

Removing "dummy" cell entries - zero length
 

Hi

many thanks - will try it out

bw

Tim

"JMay" wrote in message
...
Back up Your FILE FIRST !! &
In a standard module paste in:

Sub foo()
For Each c In UsedRange
If c.Value = "" Then
c.ClearContents
End If
Next c
End Sub

Make sure it does what you want it to before
Re-Saving your File !!!

HTH

Removing "dummy" cell entries - zero length
 

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(a17,"ok","")



Tim Childs wrote:

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

--

Dave Peterson

Removing "dummy" cell entries - zero length
 

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
"Dave Peterson" wrote in message
...
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(a17,"ok","")



Tim Childs wrote:

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

--

Dave Peterson