Delete xl range on close
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Delete xl range on close

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Calgary
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Is there a way that I can delete a specific range in an excel worksheet when the user either saved the workbook or closed the workbook

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Place these codes in the workbook module:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Range("A1").Delete

    End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Range("A1").Delete

    End Sub
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Open the VBA window by pressing Alt-F11.

    Double click on 'ThisWorkbook' under Microsoft Excel Objects.

    Change the drop-down in the window to the right that displays '(General)' to Workbook.

    Change the drop-down to the right of that to 'BeforeClose'.

    After this:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Type:
    Worksheets("SheetName").Range("A1:A10").Delete Shift:=xlShiftToLeft







  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Calgary
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This works great.

    The only problem that I'm having with it is that the background of the range I'm deleting(K29:T53) is yellow and it is deleting this as well. Something that I was hoping to keep when it was all said and done. Is there a way around this?

    This is what my code currently looks like:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Variance Analysis").Range("K29:T53").Delete Shift:=xlShiftToLeft
    End Sub

    Private Sub Workbook_Open()

    End Sub


    [ This Message was edited by: izzyq on 2002-04-02 09:40 ]

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Try the following:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Variance Analysis").Range("K29:T53").ClearContents
    End Sub


    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

User Tag List

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
  •  

 

 
DMCA.com