Delete xl range on close

[izzyq]

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

[Al Chara]

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

[kmiles]

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

[izzyq]

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 ]

[Al Chara]

Try the following:

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