Results 1 to 9 of 9

Thread: ClearContents doesn't always work

  1. #1
    New Member Murman01's Avatar
    Join Date
    Nov 2012
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default ClearContents doesn't always work

    Hi,
    Using Excel 2016, I have a yearly workbook that is broken in to months, quarters, and year-to-date worksheets. At the start of each fiscal year (April to March), I want to give the user the ability to clear all of their data entries in the entire workbook at the click of a single macro-enabled button on the SETUP worksheet. I've also want to give the ability to clear each month or quarter roll-up worksheet's data entry with a macro-enabled button on each worksheet.

    The macro for each individual month or quarter roll-up works as expected. The single button on the SETUP worksheet to clear the entire year works on all sheets until it goes to execute the "ClearContents" command for Sheet20 (the chart worksheet). The monthly sheets have different data to be cleared when compared to the quarterly and yearly worksheets; hence, I've used SELECT CASE in Module 1 in Sub ClearEntireYearCells() the "Range("E3:F4"). For some reason, "ClearContents" command doesn't work when "Case "SHEET20"", but yet the same command works on all the other sheets.

    I must have spent hours looking for what I'm missing. Any suggestions? I thank you in advance for your help!!

    Sheet2 is the setup sheet
    Sheet3 to Sheet19 are the monthly and quarterly sheets
    Sheet20 is the year-to-date sheet.


    Code:
    Public Sub ClearSingleMonthCells()
    
    
        'This will clear a single month's data cells in a specific worksheet, but leave the formulas.
    
    
        If MsgBox("This will clear all the data from the " & ActiveSheet.Name & " worksheet and it can't be undone!" & vbNewLine & vbNewLine & "Are you sure you want to continue?", vbExclamation + vbYesNo) = vbNo Then Exit Sub     'verifies with user
        On Error Resume Next
                
        With ActiveWorkbook.ActiveSheet
            Range("E3:F4").ClearContents   'clears contents of the header information.
            Range("C9:C9").ClearContents   'clears contents of # paid hours in month.
            Range("B12:C23").ClearContents   'clears monthly data but leaves the formulas.
            Range("G12:I23").ClearContents   'clears monthly data but leaves the formulas.
            Range("A29:I46").ClearContents  'clears the comments section.
        End With
    
    
        MsgBox "The monthly clear data on the " & vbNewLine & vbNewLine & ActiveSheet.Name & " worksheet" & vbNewLine & vbNewLine & " has been completed."
        
    End Sub
    
    
    
    
    
    Public Sub ClearRollupMonthCells()
    
    
        'This will clear the roll-up sheet's data cells in a specific worksheet, but leave the formulas.
    
    
        If MsgBox("This will clear all the data from the " & ActiveSheet.Name & " worksheet and it can't be undone!" & vbNewLine & vbNewLine & "Are you sure you want to continue?", vbExclamation + vbYesNo) = vbNo Then Exit Sub     'verifies with user
        On Error Resume Next
                
        With ActiveWorkbook.ActiveSheet
            Range("E3:F4").ClearContents   'clears contents of the header information.
            Range("G12:I23").ClearContents   'clears monthly data but leaves the formulas.
            Range("A29:I46").ClearContents  'clears the comments section.
        End With
    
    
        MsgBox "The monthly clear data on the " & vbNewLine & vbNewLine & ActiveSheet.Name & " worksheet" & vbNewLine & vbNewLine & " has been completed."
        
    End Sub
    
    
    
    
    Public Sub ClearChartCells()
    
    
        'This will clear all monthly data cells in SHEET20.
    
    
        If MsgBox("This will clear all the data from the " & ActiveSheet.Name & " worksheet and it can't be undone!" & vbNewLine & vbNewLine & "Are you sure you want to continue?", vbExclamation + vbYesNo) = vbNo Then Exit Sub     'verifies with user
        On Error Resume Next
                
        With ActiveWorkbook.ActiveSheet
            Range("E3:F4").ClearContents   'clears contents of the header information.
        End With
    
    
        MsgBox "The monthly clear data on the " & vbNewLine & vbNewLine & ActiveSheet.Name & " worksheet" & vbNewLine & vbNewLine & " has been completed."
        
    End Sub
    
    
    
    Public Sub ClearEntireYearCells()
        'https://www.ozgrid.com/forum/forum/help-forums/excel-general/84813-run-same-vba-macro-code-on-multiple-sheets
        'This will clear all monthly data cells in all worksheets, but leave the formulas.
    
    
        If MsgBox("This will clear all the data for the year from the entire workbook and it can't be undone!" & vbNewLine & vbNewLine & "Are you sure you want to continue?", vbExclamation + vbYesNo) = vbNo Then Exit Sub     'verifies with user
        
        On Error Resume Next     'if there are no contents in the cells, the ClearContents will give an error.  This code ignores the error and continues.
    
    
        Application.ScreenUpdating = False
         
        Dim sh As Worksheet
         
        On Error Resume Next     'if there are no contents in the cells, the ClearContents will give an error.  This code ignores the error and continues.
       '
       'The following code is to clear the monthly worksheets.
       '
        For Each sh In Worksheets
    
    
            Select Case UCase(sh.CodeName)     'SELECT CASE is case sensitive.
    
    
                Case "SHEET3", "SHEET4", "SHEET5", "SHEET7", "SHEET8", "SHEET9", "SHEET11", "SHEET12", "SHEET13", "SHEET15", "SHEET16", "SHEET17"
                    With sh          'Need the ".Range" when in a WITH statement.
                        'Clearing each single month.
                        .Range("E3:F4").ClearContents   'clears contents of the header information.
                        .Range("C9:C9").ClearContents   'clears contents of # paid hours in month.
                        .Range("B12:C23").ClearContents   'clears monthly data.
                        .Range("G12:I23").ClearContents   'clears monthly data.
                        .Range("A29:I46").ClearContents  'clears the comments section.
                    End With
                    
                Case "SHEET6", "SHEET10", "SHEET14", "SHEET18", "SHEET19"
                    With sh          'Need the ".Range" when in a WITH statement.
                        'Clearing quarterly and year to date rollups.
                        .Range("E3:F4").ClearContents   'clears contents of the header information.
                        .Range("G12:I23").ClearContents   'clears monthly data but leaves the formulas.
                        .Range("A29:I46").ClearContents  'clears the comments section.
                    End With
    
    
                Case "SHEET20"
                    'Clearing header info on CHART worksheet.
                    Range("E3:F4").ClearContents   'clears contents of the header information.
            End Select
        Next sh
        
        Worksheets(2).Activate   'Return to SHEET2
        
        Application.ScreenUpdating = True
        
        MsgBox "The yearly clear data has been completed."
        
    End Sub

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: ClearContents doesn't always work

    What happens if you change

    Code:
    For Each sh In Worksheets
    to
    Code:
    For Each sh In Sheets
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,248
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    3 Thread(s)

    Default Re: ClearContents doesn't always work

    You need to use a period/dot before the Range (ie .Range).

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: ClearContents doesn't always work

    Actually it needs sh.Range("E3:F4").ClearContents as it is outside the With statement.
    The changing of Worksheets to Sheets still stands as a chart sheet is part of the Sheets collection not the Worksheets.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,248
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    3 Thread(s)

    Default Re: ClearContents doesn't always work

    It must be a worksheet with a chart rather then a chart sheet as they dont have cells to clear.

  6. #6
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: ClearContents doesn't always work

    Quote Originally Posted by steve the fish View Post
    It must be a worksheet with a chart rather then a chart sheet as they dont have cells to clear.
    True, wasn't thinking when the OP stated it was a "chart worksheet" thanks for pointing it out.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  7. #7
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,257
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: ClearContents doesn't always work

    Cross-posted at: https://www.excelguru.ca/forums/show...-t-always-work
    Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-a...rum-rules.html
    Cheers
    Paul Edstein
    [MS MVP - Word]

  8. #8
    New Member Murman01's Avatar
    Join Date
    Nov 2012
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ClearContents doesn't always work

    Sorry about the cross-posting. As you can see, I don't post often so didn't know the rules very well. I won't make this mistake again.

    Thank you very much, MARK858. Adding the sh. before Range("E3:E4") statement solved it. (how do I mark this thread as being [SOLVED]?)

    Yes, steve the fish, it is a worksheet with a chart rather then a chart sheet.

    I appreciate everyone's assistance on this. Cheers!

  9. #9
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: ClearContents doesn't always work

    You don't mark threads as solved on this forum, you just say thank you (which you have already done
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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