Clearing all values in range that aren't formulas
 

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

Clearing all values in range that aren't formulas
 

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

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

Clearing all values in range that aren't formulas
 

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

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

"Barb Reinhardt" wrote in message
...
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

Clearing all values in range that aren't formulas
 

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

Paul B wrote:
Bard, one way,
Range("myrange").SpecialCells(xlConstants).ClearCo ntents

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

"Barb Reinhardt" wrote in message
...
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

Clearing all values in range that aren't formulas
 

Thanks. That does exactly what I want.

"John Coleman" wrote:


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

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

Clearing all values in range that aren't formulas
 

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...b;en-us;832293
The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected
in Excel



--
Regards,
Tom Ogilvy


"John Coleman" wrote in message
ups.com...
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

Paul B wrote:
Bard, one way,
Range("myrange").SpecialCells(xlConstants).ClearCo ntents

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

"Barb Reinhardt" wrote in
message
...
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

Clearing all values in range that aren't formulas
 

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

"Tom Ogilvy" wrote in message
...
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...b;en-us;832293
The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected
in Excel



--
Regards,
Tom Ogilvy


"John Coleman" wrote in message
ups.com...
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

Paul B wrote:
Bard, one way,
Range("myrange").SpecialCells(xlConstants).ClearCo ntents

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

"Barb Reinhardt" wrote in
message
...
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