Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing cells without clearing formulas | Excel Discussion (Misc queries) | |||
Clear a worksheet without clearing formulas? | Excel Discussion (Misc queries) | |||
clear the values, but not the formulas in a range | Excel Programming | |||
Select range -- convert formulas to values | Excel Programming | |||
Clearing Contents but not Formulas | Excel Worksheet Functions |