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

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

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

"Barb Reinhardt" <(E-Mail Removed)> wrote in message
news:588C587C-51CB-4978-9FC2-(E-Mail Removed)...
>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

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).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
>
> "Barb Reinhardt" <(E-Mail Removed)> wrote in message
> news:588C587C-51CB-4978-9FC2-(E-Mail Removed)...
> >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

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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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).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
>>
>> "Barb Reinhardt" <(E-Mail Removed)> wrote in
>> message
>> news:588C587C-51CB-4978-9FC2-(E-Mail Removed)...
>> >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
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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).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
>>>
>>> "Barb Reinhardt" <(E-Mail Removed)> wrote in
>>> message
>>> news:588C587C-51CB-4978-9FC2-(E-Mail Removed)...
>>> >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
>>
>
>