Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all

    I have tried googling and searching for a way to close a workbook and automatically clear just a few cells,on 1 sheet there will 2 cells and on another a range of about 60, something like A1:J60.

    I have found a few example but nothing that I can seem to adapt.

    Can anybody link me to any samples on the www that I can try to change to suit my needs

    Thank you

  2. #2
    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
    Here is an aircode example:

    Activesheet.range("A100").clearcontents
    Activesheet.range("A1:J60").clearcontents
    Activeworkbook.close true

    Of course instead of activeworkbook or activesheet, you can explicitly designate the sheet and workbook.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='Danny Williams' post='793163' date='13-Sep-2009 07:28']Hi all

    I have tried googling and searching for a way to close a workbook and automatically clear just a few cells,on 1 sheet there will 2 cells and on another a range of about 60, something like A1:J60.

    I have found a few example but nothing that I can seem to adapt.

    Can anybody link me to any samples on the www that I can try to change to suit my needs

    Thank you[/quote]
    The following code, when placed in the ThisWorkbook module and modified to your particular needs should do the trick.
    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
     
    	 With Sheets("Sheet1")
    		 .Range("B3").ClearContents
    		 .Range("C5").ClearContents
    	 End With
    	 
    	 With Sheets("Sheet2")
    		 .Range("A1:J60").ClearContents
    		 .Range("M31:P35").ClearContents
    	 End With
    	 
    	 ThisWorkbook.Save
    	 
     End Sub
    Regards
    Don

  4. #4
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='793170' date='13-Sep-2009 14:00']Here is an aircode example:

    Activesheet.range("A100").clearcontents
    Activesheet.range("A1:J60").clearcontents
    Activeworkbook.close true

    Of course instead of activeworkbook or activesheet, you can explicitly designate the sheet and workbook.

    Steve[/quote]

    Hi Steve

    Thank you for the advice

  5. #5
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='793171' date='13-Sep-2009 14:02']The following code, when placed in the ThisWorkbook module and modified to your particular needs should do the trick.
    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
     
    	 With Sheets("Sheet1")
    		 .Range("B3").ClearContents
    		 .Range("C5").ClearContents
    	 End With
    	 
    	 With Sheets("Sheet2")
    		 .Range("A1:J60").ClearContents
    		 .Range("M31:P35").ClearContents
    	 End With
    	 
    	 ThisWorkbook.Save
    	 
     End Sub
    [/quote]

    Hi Don

    That is absolutely smashing

    One word of warning to anybody else using this, it does not like merged cells, it took me a while to find but was easily cured once I did

    Thanks again Don

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='Danny Williams' post='793245' date='14-Sep-2009 01:59']Hi Don

    That is absolutely smashing

    One word of warning to anybody else using this, it does not like merged cells, it took me a while to find but was easily cured once I did

    Thanks again Don[/quote]
    A general word of advice; Avoid Merged Cells. The same effect can be achieved in the horizontal plane with:
    • Format > Cells
    • Alignment tab
    • Horizontal box
    • Center Across Selection
    Regards
    Don

Posting Permissions

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