Perform a delete on file save

**KevinThomas** · 03-01-2010, 07:37 AM

Hello All, I would like to know if there is a way to delete specific cells based on another cells value on close/save of wookbook.

For instance, if cell G2 = 1, I would like to delete B2:F2
If cell G15 = 1, I would liek to delete B15:F15
and so on down to row 2,500.

Column G will always be the deciding value cell, and cells B-F will be the ones delected if G is equal to 1.

Thanks in advance

**6StringJazzer** · 03-01-2010, 09:31 AM

You need VBA to do anything special at file save time. Since you didn't post in the Programming forum, you may not have expected this.

This Sub will do precisely what you described. It needs to be installed to the ThisWorkbook module*. This assumes you know exactly how many rows of data there are. If you don't, the code should be modified to reflect the actual structure of your data (i.e., find last row, etc.). Note that you may have to change the worksheet name to match yours. You will also be prompted when opening the workbook to allow macros to run.

Please Login or Register  to view this content.

______________________
*Press ALT-F11. Look at the folder structure on the left, find your workbook if more than one are open, and double-click on ThisWorkbook. The pane on the right is where you put the code.

**6StringJazzer** · 03-01-2010, 09:33 AM

On further thought, there might be a better to do what you want, depending on what you actually want. For example, you can populate cells B2:F2500 with a formula that will cause the cells to be blank if the G cell is 1, but that may not be what you need. Sometimes it is helpful to give a little background, and you might get suggestions for a different approach that what you originally thought of.

**MarMo** · 03-01-2010, 09:33 AM

Hi KevinThomas ,

You probably will need VBA code to do this.
If you do not know how to program in VBA , it might be a little hard.

I've attache a small file with the code needed to delete the cellcontents if value is 1.
Perhaps this could help you.
Open the file , "Enable Macro's" , then ALT+F8 or Tools - Macro's - Macro's
Select the macro and run it.
Code used for this workbook:

Sub DeleteCellContents()
'Delete contents in cell or cellrange based on another cellvalue

'Select first cell to check
Range("G2").Select
' Loop through all rows with a Do...Loop
Do

'Use If..Then..Else to determine if cellvalue is the same as 1
If ActiveCell.Value = 1 Then
Range(ActiveCell.Offset(0, -5), ActiveCell.Offset(0, -1)).ClearContents
Else
' do nothing
End If
'Select the next cell to check
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""
End Sub

If you need additional info , feel free to post on this forum.

**6StringJazzer** · 03-01-2010, 09:51 AM

Originally Posted by MarMo

I've attache a small file with the code....

Here are some suggestions:

Put any code in code tags

Operating on cells by selecting them slows things down and also causes a lot of visible activity in the sheet. It is generally better to simply operate on the cell, rather than selecting it then operating on the selection.

DeleteCellContents keeps working until it finds a blank cell in column G but I'm not sure we can assume that all G cells are non-blank.

For this to happen automatically on save, we need to use the event handler Workbook_BeforeSave

**MarMo** · 03-01-2010, 10:42 AM

6StringJazzer,
Thanks for the suggestions.
I'm not an expert and didn't work with this kind of code before (because i did not understand it very well.)
Now that i tried it myself , i can see this takes less visible activity on the screen.
I'll dig into the helpfiles on this code to learn more about this.

Thanks again.
Mario

**6StringJazzer** · 03-01-2010, 10:47 AM

Actually, here is a slightly improved version:

Please Login or Register  to view this content.

The extra calls don't make it work any better but do spare the user from having to watch cells being updated. Also, suppressing such displays makes things go faster; I/O takes a lot of time compared to calculations.