+ Reply to Thread
Results 1 to 13 of 13

Clearing Cells upon closing the document

  1. #1
    Registered User
    Join Date
    06-01-2013
    Location
    Nebraska
    MS-Off Ver
    Excel 2010
    Posts
    7

    Clearing Cells upon closing the document

    I am making a charge sheet on excel and i used the code
    Private Sub Workbook_Open()

    Range("i5").Value = Range("i5").Value + 1

    End Sub
    to create a running count every time i open the document. Now i am trying to get the document to delete information that is entered so that way every time i open the document it is cleared and ready to put information in again. I only want to clear certain cells and not the whole spreadsheet.

  2. #2
    Registered User
    Join Date
    11-09-2009
    Location
    london, england
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Clearing Cells upon closing the document

    If you know the cells that you want to clear then use the clearcontent method on the defined range to clear the contents. Note, if you use the clear method then all formatting will be removed as well.
    For more information (msdn reference): http://msdn.microsoft.com/en-us/libr...ffice.14).aspx
    Hopes this helps.
    Please click * if the answer was helpful.

  3. #3
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Clearing Cells upon closing the document

    try
    Please Login or Register  to view this content.
    will clear range on Sheet1:
    A1:B5 and C6:D9 and Q9:W22

  4. #4
    Registered User
    Join Date
    06-01-2013
    Location
    Nebraska
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Clearing Cells upon closing the document

    tehneXus i tried your code but switched it to the cells i needed cleared but i have some merged and when i go to close it say i can not change part of a merged cell.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Worksheets("Sheet1")
    Union(.Range("e5:e8"), .Range("a11:d23"), .Range("i11:j23")).ClearContents
    End With
    End Sub

    This is what i changed it to for my cells. The E cells are merged over to include H and the D cells are merged over to include H.
    Is there a way to make that work?

  5. #5
    Registered User
    Join Date
    06-01-2013
    Location
    Nebraska
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Clearing Cells upon closing the document

    tehneXus i tried your code but switched it to the cells i needed cleared but i have some merged and when i go to close it say i can not change part of a merged cell.
    Please Login or Register  to view this content.
    This is what i changed it to for my cells. The E cells are merged over to include H and the D cells are merged over to include H.
    Is there a way to make that work?

  6. #6
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Clearing Cells upon closing the document

    Hi,

    if you have merged cells you have to change the range to cover all cells merged, see attached file for examples and appropriate code: Clear.xlsm

  7. #7
    Registered User
    Join Date
    06-01-2013
    Location
    Nebraska
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Clearing Cells upon closing the document

    tehneXus how did you make that clear button that would work very well.

  8. #8
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Clearing Cells upon closing the document

    Hi,

    Enable the Developer tab as described here: http://office.microsoft.com/en-us/ex...101819080.aspx

    Developer -> Insert -> AxtiveX Controls -> CommandButton

    Enable the "Developer Mode" with the toggle button

    then double Click the button to write code for the click event

  9. #9
    Registered User
    Join Date
    06-01-2013
    Location
    Nebraska
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Clearing Cells upon closing the document

    I can get it to delete everything except the merged cells. It then comes up with cannot change part of a merged cell.

  10. #10
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Clearing Cells upon closing the document

    Could you please create a sample workbook and upload it including the some merged cells?

  11. #11
    Registered User
    Join Date
    06-01-2013
    Location
    Nebraska
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Clearing Cells upon closing the document

    Chargeticket.xlsmthis is similar to what i have been working on, I removed the merged cells out of the range so the button would work. I cant get it to work while they are included.

  12. #12
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Clearing Cells upon closing the document

    this will clear all merged cells in the example file, just remove the ranges you want to keep
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    06-01-2013
    Location
    Nebraska
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Clearing Cells upon closing the document

    You are an excel wizard. Thank you very much tehneXus. You have been very helpful. Have a wonderful day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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