| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
clearing data but retaining formulas
I have a macro that clears the contents of a range of cells; however, I would
like to keep the formulas in certain cells, just clear the data....how can I do this? Thanks. 'Clear User Lists on Control Sheet Sheets("Control").Select Range("A8:E65536").Select Selection.ClearContents Range("A5").Select Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
clearing data but retaining formulas
Instead of ClearContents, you can just assign the empty string "" to the
range. Here is your code modified to do this and with all those unnecessary Select/Selections removed (and a With/EndWith block to make it more readable)... With Sheets("Resource Details") .Range("A8:E65536") = "" .Cells(5, 1) = UCase(.Cells(5, 1)) End With Rick "pm" wrote in message ... I have a macro that clears the contents of a range of cells; however, I would like to keep the formulas in certain cells, just clear the data....how can I do this? Thanks. 'Clear User Lists on Control Sheet Sheets("Control").Select Range("A8:E65536").Select Selection.ClearContents Range("A5").Select Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
clearing data but retaining formulas
On Error Resume Next
With Sheets("Control") .Range(.Range("A8"), .Cells(Rows.Count, _ "E")).SpecialCells(xlCellTypeConstants).ClearConte nts End With On Error Goto 0 -- HTH... Jim Thomlinson "pm" wrote: I have a macro that clears the contents of a range of cells; however, I would like to keep the formulas in certain cells, just clear the data....how can I do this? Thanks. 'Clear User Lists on Control Sheet Sheets("Control").Select Range("A8:E65536").Select Selection.ClearContents Range("A5").Select Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
clearing data but retaining formulas
Hi Rick,
I tried running this macro with the empty string "", but it still erased my formula..any other suggestions? Thanks in advance. "Rick Rothstein (MVP - VB)" wrote: Instead of ClearContents, you can just assign the empty string "" to the range. Here is your code modified to do this and with all those unnecessary Select/Selections removed (and a With/EndWith block to make it more readable)... With Sheets("Resource Details") .Range("A8:E65536") = "" .Cells(5, 1) = UCase(.Cells(5, 1)) End With Rick "pm" wrote in message ... I have a macro that clears the contents of a range of cells; however, I would like to keep the formulas in certain cells, just clear the data....how can I do this? Thanks. 'Clear User Lists on Control Sheet Sheets("Control").Select Range("A8:E65536").Select Selection.ClearContents Range("A5").Select Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
clearing data but retaining formulas
On Thu, 24 Jan 2008 08:17:08 -0800, pm wrote:
I have a macro that clears the contents of a range of cells; however, I would like to keep the formulas in certain cells, just clear the data....how can I do this? Thanks. 'Clear User Lists on Control Sheet Sheets("Control").Select Range("A8:E65536").Select Selection.ClearContents Range("A5").Select Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1)) Worksheets("Control").Range("A8:E65536").SpecialCe lls(xlCellTypeConstants).ClearContents --ron |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
clearing data but retaining formulas
Sorry, I missed the "formula" part of your question. This should work...
With Sheets("Resource Details") .Range("A8:E65536").SpecialCells(xlCellTypeConstan ts).ClearContents .Cells(5, 1) = UCase(.Cells(5, 1)) End With Rick "pm" wrote in message ... Hi Rick, I tried running this macro with the empty string "", but it still erased my formula..any other suggestions? Thanks in advance. "Rick Rothstein (MVP - VB)" wrote: Instead of ClearContents, you can just assign the empty string "" to the range. Here is your code modified to do this and with all those unnecessary Select/Selections removed (and a With/EndWith block to make it more readable)... With Sheets("Resource Details") .Range("A8:E65536") = "" .Cells(5, 1) = UCase(.Cells(5, 1)) End With Rick "pm" wrote in message ... I have a macro that clears the contents of a range of cells; however, I would like to keep the formulas in certain cells, just clear the data....how can I do this? Thanks. 'Clear User Lists on Control Sheet Sheets("Control").Select Range("A8:E65536").Select Selection.ClearContents Range("A5").Select Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1)) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
clearing data but retaining formulas
Hey Ron,
That worked...now I'm getting an #N/A in my formula fields. So, I need an IF statement to blank the error??? "Ron Rosenfeld" wrote: On Thu, 24 Jan 2008 08:17:08 -0800, pm wrote: I have a macro that clears the contents of a range of cells; however, I would like to keep the formulas in certain cells, just clear the data....how can I do this? Thanks. 'Clear User Lists on Control Sheet Sheets("Control").Select Range("A8:E65536").Select Selection.ClearContents Range("A5").Select Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1)) Worksheets("Control").Range("A8:E65536").SpecialCe lls(xlCellTypeConstants).ClearContents --ron |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
clearing data but retaining formulas
Thanks Rick,
How do I get rid of the error N/A in the formula fields, please.....Thanks. "Rick Rothstein (MVP - VB)" wrote: Sorry, I missed the "formula" part of your question. This should work... With Sheets("Resource Details") .Range("A8:E65536").SpecialCells(xlCellTypeConstan ts).ClearContents .Cells(5, 1) = UCase(.Cells(5, 1)) End With Rick "pm" wrote in message ... Hi Rick, I tried running this macro with the empty string "", but it still erased my formula..any other suggestions? Thanks in advance. "Rick Rothstein (MVP - VB)" wrote: Instead of ClearContents, you can just assign the empty string "" to the range. Here is your code modified to do this and with all those unnecessary Select/Selections removed (and a With/EndWith block to make it more readable)... With Sheets("Resource Details") .Range("A8:E65536") = "" .Cells(5, 1) = UCase(.Cells(5, 1)) End With Rick "pm" wrote in message ... I have a macro that clears the contents of a range of cells; however, I would like to keep the formulas in certain cells, just clear the data....how can I do this? Thanks. 'Clear User Lists on Control Sheet Sheets("Control").Select Range("A8:E65536").Select Selection.ClearContents Range("A5").Select Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1)) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
clearing data but retaining formulas
Incorporate this into your code or just run it in tandem with your other code.
Sub NATrapAdd() Dim myStr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=IF(ISNA*" Then myStr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")" End If End If Next End Sub Gord Dibben MS Excel MVP On Fri, 25 Jan 2008 14:48:01 -0800, pm wrote: Hey Ron, That worked...now I'm getting an #N/A in my formula fields. So, I need an IF statement to blank the error??? "Ron Rosenfeld" wrote: On Thu, 24 Jan 2008 08:17:08 -0800, pm wrote: I have a macro that clears the contents of a range of cells; however, I would like to keep the formulas in certain cells, just clear the data....how can I do this? Thanks. 'Clear User Lists on Control Sheet Sheets("Control").Select Range("A8:E65536").Select Selection.ClearContents Range("A5").Select Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1)) Worksheets("Control").Range("A8:E65536").SpecialCe lls(xlCellTypeConstants).ClearContents --ron |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
clearing data but retaining formulas
Normally, you would hand that in the formula itself by checking for the
error with an IF function call and returning "" if there is an error otherwise return your formula. See Gord Dibben's post (located under Ron Rosenfeld message) for an addition to the macro that will handle modifying your formula for you (but keep in mind the idea for future formulas you write). Rick "pm" wrote in message ... Thanks Rick, How do I get rid of the error N/A in the formula fields, please.....Thanks. "Rick Rothstein (MVP - VB)" wrote: Sorry, I missed the "formula" part of your question. This should work... With Sheets("Resource Details") .Range("A8:E65536").SpecialCells(xlCellTypeConstan ts).ClearContents .Cells(5, 1) = UCase(.Cells(5, 1)) End With Rick "pm" wrote in message ... Hi Rick, I tried running this macro with the empty string "", but it still erased my formula..any other suggestions? Thanks in advance. "Rick Rothstein (MVP - VB)" wrote: Instead of ClearContents, you can just assign the empty string "" to the range. Here is your code modified to do this and with all those unnecessary Select/Selections removed (and a With/EndWith block to make it more readable)... With Sheets("Resource Details") .Range("A8:E65536") = "" .Cells(5, 1) = UCase(.Cells(5, 1)) End With Rick "pm" wrote in message ... I have a macro that clears the contents of a range of cells; however, I would like to keep the formulas in certain cells, just clear the data....how can I do this? Thanks. 'Clear User Lists on Control Sheet Sheets("Control").Select Range("A8:E65536").Select Selection.ClearContents Range("A5").Select Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1)) |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
clearing data but retaining formulas
I left out the part that should have reminded OP that his formulas should
contain the ISNA trap long before he reaches the stage he is at. By habit all formulas should be error-trapped for whatever type of error is expected. Thanks for pointing that out Rick. Gord On Fri, 25 Jan 2008 20:16:32 -0500, "Rick Rothstein \(MVP - VB\)" wrote: Normally, you would hand that in the formula itself by checking for the error with an IF function call and returning "" if there is an error otherwise return your formula. See Gord Dibben's post (located under Ron Rosenfeld message) for an addition to the macro that will handle modifying your formula for you (but keep in mind the idea for future formulas you write). Rick "pm" wrote in message ... Thanks Rick, How do I get rid of the error N/A in the formula fields, please.....Thanks. "Rick Rothstein (MVP - VB)" wrote: Sorry, I missed the "formula" part of your question. This should work... With Sheets("Resource Details") .Range("A8:E65536").SpecialCells(xlCellTypeConstan ts).ClearContents .Cells(5, 1) = UCase(.Cells(5, 1)) End With Rick "pm" wrote in message ... Hi Rick, I tried running this macro with the empty string "", but it still erased my formula..any other suggestions? Thanks in advance. "Rick Rothstein (MVP - VB)" wrote: Instead of ClearContents, you can just assign the empty string "" to the range. Here is your code modified to do this and with all those unnecessary Select/Selections removed (and a With/EndWith block to make it more readable)... With Sheets("Resource Details") .Range("A8:E65536") = "" .Cells(5, 1) = UCase(.Cells(5, 1)) End With Rick "pm" wrote in message ... I have a macro that clears the contents of a range of cells; however, I would like to keep the formulas in certain cells, just clear the data....how can I do this? Thanks. 'Clear User Lists on Control Sheet Sheets("Control").Select Range("A8:E65536").Select Selection.ClearContents Range("A5").Select Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Clear a worksheet without clearing formulas? | Excel Discussion (Misc queries) | |||
| retaining formulas | Excel Worksheet Functions | |||
| retaining functions or formulas in cells | Excel Worksheet Functions | |||
| How do I clear a column of data without clearing the formulas? | Excel Discussion (Misc queries) | |||
| Clearing Contents but not Formulas | Excel Worksheet Functions | |||
Linear Mode
