My range is c10:d1000. The cells have a data link (DDE) in them. The value of
the cell can be 0 to 3000.

Could a macro clear the contents of cells were the value is 0 ?

Thank you in advance.

yes it could.

Right click the sheet tab and paste this in

Sub stantiate()
Dim myRange As Range
Set myRange = Range("C10:d1000")
For Each c In myRange
If c.Value = 0 Then
c.Value = ""
End If
Next
End Sub


Mike

"carl" wrote:

> My range is c10:d1000. The cells have a data link (DDE) in them. The value of
> the cell can be 0 to 3000.
>
> Could a macro clear the contents of cells were the value is 0 ?
>
> Thank you in advance.

Sub Clear_Cells_With_Zero()
findstring = "0"
Set B = Range("C:C").Find(What:=findstring, LookAt:=xlWhole)
While Not (B Is Nothing)
B.ClearContents
Set B = Range("C:C").Find(What:=findstring, LookAt:=xlWhole)
Wend
End Sub

Without using a macro you should be able to use Data>Filter>Autofilter on column
C to find cells with zero and clear contents.


Gord Dibben MS Excel MVP

On Thu, 5 Jul 2007 09:24:02 -0700, carl <(E-Mail Removed)> wrote:

>My range is c10:d1000. The cells have a data link (DDE) in them. The value of
>the cell can be 0 to 3000.
>
>Could a macro clear the contents of cells were the value is 0 ?
>
>Thank you in advance.

Hi. Thank you for helping me.

I ran the code and it got hung up here:

If c.Value = 0 Then

I should have mentioned that the cell value vould be #N/A or some other
"errors".

Could this be the reason the code gets hung up ?

If so, is there a way to resolve the problem ?

Thank you in advance.

"Mike H" wrote:

> yes it could.
>
> Right click the sheet tab and paste this in
>
> Sub stantiate()
> Dim myRange As Range
> Set myRange = Range("C10:d1000")
> For Each c In myRange
> If c.Value = 0 Then
> c.Value = ""
> End If
> Next
> End Sub
>
>
> Mike
>
> "carl" wrote:
>
> > My range is c10:d1000. The cells have a data link (DDE) in them. The value of
> > the cell can be 0 to 3000.
> >
> > Could a macro clear the contents of cells were the value is 0 ?
> >
> > Thank you in advance.