Remove Data from Empty Cells

[alish]

Hi all,
How do i remove data from the cells that have been pasted as value? The
cells were originally formulas but i have copy-special-value pasted. some of
them are blank but when i do COUNT it counts. how do i remove or clear-all
hundreds of cells at once? there are also cells with the info among them.
Please help. Thanks.

 

[Max]

You could try the sub below (by Jay) to clear cells
with residual zero length null strings: "" within a selected range

Just select the range(s), run the sub

Sub ClearNulls()
Set rng = Selection
For Each ar In Selection.Areas
For Each itm In ar
If Trim(itm.Value) = "" _
Then itm.ClearContents
Next 'itm
Next 'ar
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik

 

[alish]

Great!!! It worked. Thank you.

You could try the sub below (by Jay) to clear cells
with residual zero length null strings: "" within a selected range

Just select the range(s), run the sub

Sub ClearNulls()
Set rng = Selection
For Each ar In Selection.Areas
For Each itm In ar
If Trim(itm.Value) = "" _
Then itm.ClearContents
Next 'itm
Next 'ar
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik

 

[Dave Peterson]

Just a note...

I like to use something like this:

Sub ClearNulls()
dim rng as range
dim itm as range
dim ar as range

Set rng = Selection

For Each ar In rng.Areas
For Each itm In ar.cells 'added .cells
If Trim(itm.Value) = "" _
Then itm.ClearContents
Next 'itm
Next 'ar
End Sub

But in this case, you could drop the area portion of the code (since you're
looping through each cell anyway):

Sub ClearNulls()
dim rng as range
dim itm as range

Set rng = Selection

For Each itm In rng.cells
If Trim(itm.Value) = "" Then
itm.ClearContents
end if
Next 'itm

end sub

(I don't like the single line If statement -- especially when they take two
lines <vbg>.)

 

[Max]

Welcome. Pl acknowledge by pressing the YES buttons (like the one below) in
that response
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik

 

[Max]

Thanks for the thoughts and learnings, Dave !
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik