+ Reply to Thread
Results 1 to 3 of 3

Multiple Sheet Clear Cells

Hybrid View

  1. #1
    Registered User
    Join Date
    04-28-2009
    Location
    Pert, WA, Australia
    MS-Off Ver
    2010
    Posts
    24

    Multiple Sheet Clear Cells

    Hi, I've had a search for what I am looking for but can't seem to find it.

    I have multiple user defined cells across multiple sheets in my work book that I would like a macro to clear, in order to use the sheet from blank.

    I tried doing with with record macro, but it was activating the sheets & cells and it looks very messy, I tried to remove the activate sheets & cells from the VBA, but that didn't seem to help.

    Basically I want to just click a button and all required cells are cleared, no changing sheet.

    I am using Excel 2010.

    I tried with this VBA formula, but it's not liking it, I'm fairly sure I've coded it wrong, but what I am looking for is how to make it right.

    Sub Clear_All()
        Dim aRange, bRange, cRange, dRange, eRange, fRange As Range
        Set aRange = Sheets("Documentation").Cells("L2:M85,C3:K5,C7:K9,C11:K13,C15:K17,C19:K21,C23:K25,C27:K29,C31:K33,C35:K37,C39:K41,C43:K45,C47:K49,C51:K53,C55:K57,C59:K61,C63:K65,C67:K69,C71:K73,C75:K77,C79:K81,C83:K85") 'Documentation Inputs
        Set bRange = Sheets("Personnel").Cells("L2:M61,C3:K5,C7:K9,C11:K13,C15:K17,C19:K21,C23:K25,C27:K29,C31:K33,C35:K37,C39:K41,C43:K45,C47:K49,C51:K53,C55:K57,C59:K61") 'Personnel inputs
        Set cRange = Sheets("Site_Readiness").Cells("L2:M97,C3:K5,C7:K9,C11:K13,C15:K17,C19:K21,C23:K25,C27:K29,C31:K33,C35:K37,C39:K41,C43:K45,C47:K49,C51:K53,C55:K57,C59:K61,C63:K65,C67:K69,C71:K73,C75:K77,C79:K81,C83:K85,C87:K89,C91:K93,C95:K97") 'Site Readiness inputs
        Set dRange = Sheets("Information").Cells("C4:C7") 'Removes Project Information
        Set eRange = Sheets("Actions").Cells("AJ48:BV62") 'Removes Executive Summary
        Set fRange = Sheets("Attendees").Cells("B5:E37,G5:G37") 'Clears Attendance Register
        aRange.ClearContents
        bRange.ClearContents
        cRange.ClearContents
        dRange.ClearContents
        eRange.ClearContents
        fRange.ClearContents
    End Sub
    All help appreciated.

  2. #2
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Multiple Sheet Clear Cells

    change the .Cells to .Range and try
    -If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".

    -Always upload a workbook before start your question
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    +++ If my answer(s) helped you, please add me reputation by click on * +++

  3. #3
    Registered User
    Join Date
    04-28-2009
    Location
    Pert, WA, Australia
    MS-Off Ver
    2010
    Posts
    24

    Re: Multiple Sheet Clear Cells

    Brilliant, Thank you for being so quick.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1