Delete an 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 Help Me Obi Wan Kenobi, You're My Only Hope.

 

[dianal]

Yes. There are 2 events for object Workbook you need : BeforeSave and BeforeClose.
Write for VBA object ThisWorkbook 2 subs:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
'This will disable alert for saving file
'and will use default answer yes
Dim rng As Range
Set rng = Worksheets("todel&quot.Range("A1:A3&quot
'set range you want to clear
rng.Clear
Application.DisplayAlerts = True
'to give Excel possibility to display other alerts
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
Dim rng As Range
Set rng = Worksheets("todel&quot.Range("A1:A3&quot
rng.Clear
Application.DisplayAlerts = True
End Sub


I would suggest to protect you VBA project with password, so noone will be able to delete this feature without password.

 

[izzyq]

Hey Dianal,
This works great. Thank you

Another question that has come up from using this code is that the range that I am deleting(K30:T53) has a yellow background and it is being deleted as well. Something that I didn't want to happen when it's all said and done. Is there a way around this one?

This is the code that I'm currently using:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
'This will disable alert for saving file
'and will use default answer yes
Dim rng As Range
Set rng = Worksheets("Variance Analysis&quot.Range("K30:T53&quot
'set range you want to clear
rng.Clear
Application.DisplayAlerts = True
'to give Excel possibility to display other alerts
End Sub

Private Sub Workbook_Open()

End Sub
Help Me Obi Wan Kenobi, You're My Only Hope.

 

[acron]

Hi izzyq

replace rng.Clear with rng.ClearContents

A.C.

 

[dianal]

acron is correct.
You have many methods for clearing.
Clear - applies to ChartArea, Legend, or Range object and clears the entire object.
ClearComments - clears all cell comments from the specified range.
ClearContents - clears the formulas from the range. Clears the data from a chart but leaves the formatting.
ClearFormats - clears the formatting of the object.
You can check by yourself other methods in VBA help in MS Office or in MSDN.
Success!