Results 1 to 3 of 3

Thread: ClearContents doesn't always work

  1. #1
    Neophyte murman01's Avatar
    Join Date
    May 2015
    Posts
    4
    Articles
    0
    Excel Version
    2016

    ClearContents doesn't always work



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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've attached the workbook.

    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.

  2. #2
    Acolyte macropod's Avatar
    Join Date
    Mar 2017
    Posts
    40
    Articles
    0
    Excel Version
    2010
    Cross-posted at: https://www.mrexcel.com/forum/excel-...st5265177.html
    For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
    Cheers,
    Paul Edstein
    [MS MVP - Word]

  3. #3
    Neophyte murman01's Avatar
    Join Date
    May 2015
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Sorry about the cross-posting. As you can see, I don't post often so didn't know the rules. I won't make this mistake again.

    Solution: the "Range("E3:F4").ClearContents" needs to be "sh.Range("E3:F4").ClearContents" as it is outside the With statement.

Tags for this Thread

Posting Permissions

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