Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Range
Want to clear a range of cells having formulas in the cells within the
range(eg. a1:ai1) but don't want to lose the formulas in the cleared range. Is there a solution? Thanks for everyone's input in helping me with problem resolutions. Regards to all... Ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Range
Ron,
I think this is what you want to do? '------------------------------------------ Sub SelectionClearTest() ' Jim Cone - San Francisco, CA - September 07, 2004 ' Clears cells in selection except for formulas and their precedents. Dim objRngPrec As Excel.Range Dim objRngForm As Excel.Range Dim objRngBoth As Excel.Range Dim objRngAll As Excel.Range Dim objRng As Excel.Range Set objRngAll = Selection On Error Resume Next Set objRngForm = objRngAll.SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If objRngForm Is Nothing Then MsgBox "No Formulas in Selection. " Set objRngAll = Nothing Exit Sub End If On Error Resume Next Set objRngPrec = objRngForm.Precedents On Error GoTo 0 If objRngPrec Is Nothing Then Set objRngPrec = objRngForm End If Set objRngBoth = Application.Union(objRngForm, objRngPrec) For Each objRng In objRngAll.Cells If Application.Intersect(objRng, objRngBoth) Is Nothing Then objRng.ClearContents End If Next 'objRng Set objRngPrec = Nothing Set objRngForm = Nothing Set objRngBoth = Nothing Set objRngAll = Nothing Set objRng = Nothing End Sub '------------------------------------------ Regards, Jim Cone San Francisco, CA "Ronald Cayne" wrote in message ... Want to clear a range of cells having formulas in the cells within the range(eg. a1:ai1) but don't want to lose the formulas in the cleared range. Is there a solution? Thanks for everyone's input in helping me with problem resolutions. Regards to all... Ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Range
Doesn't seem to work. Range not cleared when routine is run. More specifically. I want to clear range a2:aa15 of all data and copy this data to sheet2 a2:aa15.FORMULAS TO BE LEFT INTACT. Each time I move the block from sheet1 to sheet2 I want to copy the entire block and paste it in sheet2. Sheet1 a2 should increment by 1 each time a block is moved. As each block is moved to Sheet2 it should be offset by 1 row(ie. below the previous row if data is in already present Hope this is not too muddy. In any case do you take on projects. Ron. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Range
Ron,
Did you select the entire range, including the formulas, before running the code? Note - any cells referenced by the formulas will not be cleared. If you want to also clear cells referenced by the formulas then disable this line of code: "Set objRngPrec = objRngForm.Precedents" It is about the 19th line of code, depending on how you count lines. (enter a single apostrophe ' at the beginning of the line) Note - clearing cells referenced by formulas can result in #Ref error values in the formulas. Projects? - Yes, on a fee basis - remove XXX from my email address. Regards, Jim Cone San Francisco, CA XX "Ronald Cayne" wrote in message ... Doesn't seem to work. Range not cleared when routine is run. More specifically. I want to clear range a2:aa15 of all data and copy this data to sheet2 a2:aa15.FORMULAS TO BE LEFT INTACT. Each time I move the block from sheet1 to sheet2 I want to copy the entire block and paste it in sheet2. Sheet1 a2 should increment by 1 each time a block is moved. As each block is moved to Sheet2 it should be offset by 1 row(ie. below the previous row if data is in already present Hope this is not too muddy. In any case do you take on projects. Ron. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Range
See my question on here "Debug Problem" there is an elegant solution
John Ronald Cayne wrote: Want to clear a range of cells having formulas in the cells within the range(eg. a1:ai1) but don't want to lose the formulas in the cleared range. Is there a solution? Thanks for everyone's input in helping me with problem resolutions. Regards to all... Ron -- R-e-t-u-r-n A-d-d-r-e-s-s I-n-s-t-r-u-c-t-i-o-n-s Change LID to |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting/Clearing duplicate range of cells | Excel Worksheet Functions | |||
Clearing cells without clearing formulas | Excel Discussion (Misc queries) | |||
Clearing #VALUE | Excel Worksheet Functions | |||
Clearing a Column range in Multiple Workbooks | Excel Programming | |||
Accessing/Clearing range in hidden workbook | Excel Programming |