Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    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.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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:
    Code:
    Sub ClearData()
    
        ActiveSheet.Protect Contents:=False
        Selection.SpecialCells(xlCellTypeConstants, 23).Select
        Selection.ClearContents
        ActiveSheet.Protect Contents:=True
        
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    ...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

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    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.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.

    Code:
    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 ***
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    Point well taken.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    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..

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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?

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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...

  15. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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:
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •