Deleting data, saving formula

[dmcnab]

Hi loungers...I have a s/sheet with about 30 col and 1100 rows....about 33,000 cells. Throughout the s/sheet, there are numerous formula.....the s/sheet is locked.

I am looking for some sort of macro that will allow me to select all 33,000 cells in the range, delete all data (the data is in unprotected cells) and leave the formulae intact....at the moment, I get the usual message that the worksheet is protected and that I have to unprotect the sheet.....and of course if I do that and do a global delete, it removes the formulae.......I have tried using a REPLACE {data} with 'nothing'...or FIND cells that do not have formula and delete, but can't get anything to work....any ideas? Thanks.

[RetiredGeek]

You can accomplish this by using the GoTo key {F5}, selecting "Special...", then "Constants", "OK".
Next right click on any of the highlighted cells and select "Clear Contents".
Note: If any of your numbers are entered with a leading = sign they will not be selected (they're considered formulas) or cleared.
Note: This will not work on a Protected Sheet!

If you want to use code:

Sub ClearData()

    ActiveSheet.Protect Contents:=False
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
    ActiveSheet.Protect Contents:=True
    
End Sub

[dmcnab]

...hmmm...thanks RG.....when I run the macro, it hangs at line 9 (shown in bold)...??..??

Sub CLEAR_DATA()
'
'CLEAR_DATA Macro
'
ActiveSheet.Unprotect Password:="123"
Range("B11:B25").Select
Selection.ClearContents
Range("o55").Select
Selection.ClearContents
Range("s2:s2").Select
Selection.ClearContents
Range("l11:bq1108").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingRows:=True, AllowFormattingCells:=True, AllowSorting:=True _
, AllowFiltering:=True, Password:="123"
Range("s5").Select
End Sub

[RetiredGeek]

What error message are you getting?

I'd try combining it with the line above it as:
Range("l11:bq1108").SpecialCells(xlCellTypeConstan ts, 23).Select

[dmcnab]

Altho the sheet is protected, I have an UNPROTECT in the code.....and so, the code I am using the code is shown below; it starts to run and then stops highlighting the line shown in bold and says "NO CELLS WERE FOUND"

ActiveSheet.Unprotect Password:="123"
Range("B11:B25").Select
Selection.ClearContents
Range("O5:O5").Select
Selection.ClearContents
Range("S2:S2").Select
Selection.ClearContents
Range("L11:BQ1108").SpecialCells(xlCellTypeConstan ts, 23).Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingRows:=True, AllowFormattingCells:=True, AllowSorting:=True _
, AllowFiltering:=True, Password:="123"
Range("s5").Select
End Sub

[sdckapr]

If you get that error on that line, it means that in that range, no cells were found that contain contstants, they are either blank of have values...

Steve

PS you do not need to select to clear contents. Anytime you have a line end in ".Select" and the next line starts with "Selection, you can eliminate both of them

For example, instead of:

Range("B11:B25").Select
Selection.ClearContents

Use:
Range("B11:B25").ClearContents

Now the range does not have to be selected (saving time) and the code is shorter and more readable.

[dmcnab]

Ah..ha...now I get it Steve...thank you.....I had run it already and it cleared the cells....when I ran it again, to double check it, there was nothing to clear and so I go the error...thank you...and thank you as well, RG, for your help.

[sdckapr]

I had run it already and it cleared the cells

If this will be an issue, you can prevent the run-time error message by ignoring the error (and re-enabling it after the line):

on error resume next
Range("L11:BQ1108").SpecialCells(xlCellTypeConstan ts, 23).ClearContents
on error goto 0

Steve

[RetiredGeek]

Ok Guys,

Taking all your input here's my attempt at a generalized Constant clearing function. Any constructive comments are welcome. See the comments for usage information.

Function bClearDataConstants(Optional vDataRange As Variant) As Boolean

'Arguments: vDataRange should be passed as a string,
'                      i.e. "A5:G32"
'                      If ommited defaults to "A1" which is
'                      the same as the whole worksheet!
'Note: a number entered as =45 is considered a formula NOT a constant!
'      Also text is considered a constant so be careful you don't
'      include your column/row titles in the range to clear!

   Dim bProtectedSheet As Boolean
   
   If IsMissing(vDataRange) Then vDataRange = "A1"
   
   If ActiveSheet.ProtectContents Then
     ActiveSheet.Protect Contents:=False
     bProtectedSheet = True
   End If
    
   On Error GoTo ErrorHandler
   Range(vDataRange).SpecialCells(xlCellTypeConstants, 23).ClearContents
   On Error GoTo 0
   
   If bProtectedSheet Then ActiveSheet.Protect Contents:=False
   
   bClearDataConstants = True
   
   Exit Function
   
ErrorHandler:
   Select Case Err.Number
         Case 1004        '*** No Data to Clear in specified range ***
             Resume Next
         Case Else
             MsgBox "An Unexpected Error: {" & Str(Err.Number) & " : " & _
             Err.Description & "} occured." & vbCrLf & vbCrLf & _
             "Please report this to the applicaion developer.", _
             vbOKOnly + vbCritical, "Error Clearing Constants"
             bClearDataConstants = False
   End Select
   
End Function     '*** bClearDataConstants ***

[sdckapr]

If you are going to create an error handler, I would recommend just using that. Put the line:
On Error GoTo ErrorHandler

near the beginning of code, and delete the line:
On Error GoTo 0

And your error routine will handle all the errors.

The "On Error GoTo 0" turns error handling off. Your code as it is, has no error-handling except for the one line (which my "resume next" just ignored if there was an error). The rest of the time will stop the code and and let VB handle the error...

Steve

[RetiredGeek]

Steve,

Point well taken.

[dmcnab]

Thanks, guys, for all your help and suggestions....for my purposes, I think that RG and Steve have nailed it......especially, now that I understand that any error message I was getting was b/c the code was working as it should (instead of 'as it shouldn't'....a rare occurrence).......now sure about the others who have been part of this, but as far as I am concerned, Hans (or whoever is moderating this) can close it....thanks again..

[Arcturus16a]

We enter data daily from a series of reports into a workbook which consolidates the results. The workbook contains many ranges, each of which balance back to the original printed reports. If the workbook needs to be modified to match changes in the original reports, the macro to clear the contents must also be changed.

So I tried this code by RG from early in this thread:
Sub ClearData()
ActiveSheet.Protect Contents:=False
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents
ActiveSheet.Protect Contents:=True
End Sub

Wiped out everything (good), including all the text labels (not so good). A macro that selects only numeric constants would greatly simplify the code we use to reset the report each day. Is there something about RG's code that can be modified to select only values? Suppose a user does an in-cell calculation?

[sdckapr]

Change the line to
Selection.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents

Steve

PS to clarify

The "23" in the code comes from adding 4 constants:

xlErrors+xlLogical+xlNumbers+xlTextValues
16 + 4 +1 + 2 = 23

With either CellTypeConstants or xlCellTypeFormulas you can set various combinations of errors, logical, numbers, or text by adding the various items. Instead of "23" the code could have used (to make it more clear):

Selection.SpecialCells(xlCellTypeConstants, _
xlErrors+xlLogical+xlNumbers+xlTextValues).ClearCo ntents

and indicate that Errors, logical constants, numbers and text should all be cleared...

You could also use:
Selection.SpecialCells(xlCellTypeConstants, 1).ClearContents

but I think using the variable name makes it a little more clear, especially when you review the code years from now...

[RetiredGeek]

Per the generalized (somewhat) code above you can change the values in the arguments to SpecialCells as follows to achieve your desired results. Also you should limit the range passed to SpecialCells to avoid erasing headings as noted in the comments of the code. Thus the number 23 in the cited example will clear Constants that are Errors, Logicals, Numbers, and Text Values.

Extracted from the VBA Help Files and the Object Browser: