Deleting values not formulas

[Guest]

Hi All,
I'm looking for a way to delete cells with values but without formulas in
it? (VBA) in office 2003.
Thanks a lot in advance

 

[papou]

Hi
For Each c In Selection.Cells
If Not c.HasFormula Then c.ClearContents
Next c

HTH
Cordially
Pascal

 

[Guest]

Thanks a lot papou.
Thats what I was looking for.
How could I forget .Hasformula!!!!

 

[Dave Peterson]

Another way to get them all at once:

Select the range to fix
Edit|Goto special|Constants
click ok
hit the delete key on the keyboard.

In code:

On error resume next
Selection.Cells.SpecialCells(xlTextValues).ClearContents
on error goto 0

You could specify a more exact range, too:

Activesheet.range("a1:c99").Cells.SpecialCells(xlTextValues).ClearContents

 

[Guest]

Thanks Dave.
This is even more elegant!!!

Another way to get them all at once:

Select the range to fix
Edit|Goto special|Constants
click ok
hit the delete key on the keyboard.

In code:

On error resume next
Selection.Cells.SpecialCells(xlTextValues).ClearContents
on error goto 0

You could specify a more exact range, too:

Activesheet.range("a1:c99").Cells.SpecialCells(xlTextValues).ClearContents