Clearing all values in range that aren't formulas

[Guest]

I have a range called myRange and I'd like to clear out all values that
aren't formulas. How would I do this programmatically?

Thanks

 

[John Coleman]

Sub ClearNonFormulas()
Dim cl As Range, myRange As Range
Set myRange = Range("myRange")
For Each cl In myRange.Cells
If cl.HasFormula = False Then cl.ClearContents
Next cl
End Sub

HTH
-John Coleman

 

[Paul B]

Bard, one way,
Range("myrange").SpecialCells(xlConstants).ClearContents

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[John Coleman]

You are being modest, you should almost say "the only way". I created a
range with about 200,000 cells and my looping-through-the cells
checking the hasformula property strategy took several minutes but the
shorter code you gave works in about 10 seconds. Pretty nice speed-up!

Thanks for educating me a bit.

-John Coleman

 

[Guest]

Thanks. That does exactly what I want.

Sub ClearNonFormulas()
Dim cl As Range, myRange As Range
Set myRange = Range("myRange")
For Each cl In myRange.Cells
If cl.HasFormula = False Then cl.ClearContents
Next cl
End Sub

HTH
-John Coleman

 

[Tom Ogilvy]

It is great, but
Just be aware that if there are more than 8192 separate cells (separate
areas), then this will fail without warning. (it will appear to work but
won't)

http://support.microsoft.com/default.aspx?scid=kb;en-us;832293
The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected
in Excel

 

[Paul B]

Tom, thanks I did not know about that.

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003