Hi, I want a quick way of clearing all zero-length strings from a spreadsheet

I tried using Find & Replace

Find: ""
Replace: (left blank)

But it put zeroes in instead of actuall deleting all contents.

??

Not for me !

So you must have some sort of custom format on the cell


Steve

On Wed, 21 Feb 2007 14:11:06 -0000, Matt D Francis
wrote:

Hi, I want a quick way of clearing all zero-length strings from a
spreadsheet

I tried using Find & Replace

Find: ""
Replace: (left blank)

But it put zeroes in instead of actuall deleting all contents.

??

Strange. They're just "General" as far as I know, I can't replicate it now
anyway!

Try this in a new sheet, formatted "General"

Column A

Value
1
1
a
a
=""
=""
(leave blank)
(leave blank)

Column B

Num
1
2
3
4
5
6
7
8

Now select A1:C9 and sort (with header row) on Value. You should get

value num
1 1
1 2
5
6
a 3
a 4
7
8

Rows 5 & 6 , although appearing blank jump up the sort order to appear after
numbers but before text, whereas the true blanks are left to the end.

That's what I'm trying to avoid.

Copy, Paste Special Values, doesn't help

Find "", replace (blank) says value not found.


???????

Any suggestions?


"SteveW" wrote:

Not for me !

So you must have some sort of custom format on the cell


Steve

On Wed, 21 Feb 2007 14:11:06 -0000, Matt D Francis
wrote:

Hi, I want a quick way of clearing all zero-length strings from a
spreadsheet

I tried using Find & Replace

Find: ""
Replace: (left blank)

But it put zeroes in instead of actuall deleting all contents.

??

If you have zero length strings in cells that are returned by formulae, then
to remove them enter and run:

Sub clear_nothing()
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
If Len(r.Value) = 0 Then
r.Clear
End If
Next
End Sub

--
Gary''s Student
gsnu200707


"Matt D Francis" wrote:

Hi, I want a quick way of clearing all zero-length strings from a spreadsheet

I tried using Find & Replace

Find: ""
Replace: (left blank)

But it put zeroes in instead of actuall deleting all contents.

??