Remove Data from Empty Cells

  • Thread starter Thread starter alish
  • Start date Start date
A

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.
 
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
 
Great!!! It worked. Thank you.

Max said:
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
Click to expand...
 
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>.)
 
Back
Top