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
---
"alish" wrote:
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.

I've run into this several times myself. Odd, huh?

Paste this code into a macro, run it after changing the top range to
aim at your range or column.

Code:
--------------------
Sub ClearNull()
For Each cell In Range("B:B")
If cell.Value = "" Then
cell.ClearContents
End If
Next
End Sub
--------------------


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45350

Great!!! It worked. Thank you.

"Max" wrote:

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" wrote:
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.

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 wrote:

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" wrote:
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.

--

Dave Peterson

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
---
"alish" wrote:
Great!!! It worked. Thank you.

Thanks for the thoughts and learnings, Dave !
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Dave Peterson" wrote in message
...
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.)