Delete xl range on close

izzyq

New Member
Joined
Mar 24, 2002
Messages
20
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Try the following:

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

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top