Code for deleting values also deletes formulas

[ipbr21054]

Hi,
In my workbook i have 12 worksheets.
Not all worksheets are used every time due to sales etc.
I use the code below to clear the values from the cells within there ranges on each sheet BUT i wish to leave the formulas intact.

I have noticed that when i run the code various formulas are deleted & i manually need to put them back.

Do you see a reason why this happens as it defeats the object.

The code shown below is the same for each page so here is a partial piece of it for you.

With ThisWorkbook.Sheets("INCOME (1)")
Application.EnableEvents = False
On Error Resume Next
.Range("A4:G28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("INCOME (2)")
Application.EnableEvents = False
On Error Resume Next
.Range("A5:G28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
.Range("C4:G4").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With

So here is an example.
Worksheet INCOME 1
Code is run & then looking at the sheet the fomulas in the cells below are deleted.

D4:D8 D9 onwards are all intact
F4:F7 F8 onwards are all intact

 

[JLGWhiz]

The ClearContents command will clear the cell of whatever is in it, including formulas, without shifting the other cells in that row and column. The only way to remove values from a cell where the value is derived by formula, and leave the formula intact, is to reduce the precedent cells (the cell or cells with source data) to zero or blank. Example: cell C5 contains the formula =IF(A5+B5 > 0,A5+B5,""). To make C5 appear to be blank you woul have to reduce both A5 and B5 to zero or blank, but the formula in C5 would still be intact. To complicate the issue, if A5 and B5 were both derived by formula, then you would need to go to their precedent cells to get C5 to display as blank. It is one of the drawbacks to using formulas, but a well planned database can avoid these conflicts.

 

[ipbr21054]

Ok i now understand.

Ive added a screen shot for some advice & to explain.

Cell B5 has had a specific value pasted there so then the cells in D5 & F5 then gets populates.
So by deleting the value in B5 also will blank out the value in D5 & F5 which is fine.

The cell vale in B4 "NOT SPECIFIC" is manually entered & so is the values in D4 E4 F4

What can i do about these cells or do i just put up with it ?

 

[JLGWhiz]

Sound like B4:F4 could be cleared without affecting any other cells. But that may not be true because another cell could be dependent on the value of one of those cells like D5 and F5 are dependent on B5. To check for which cells you can just clear without affectihng any other cells, select the cell and then click the "Trace Precedents" and "Trace Depedents" buttons under the 'Formulas' tab on the ribbon. If there are no precedents or dependents then it can be cleared without any problem.

The answer is to understand how the Excel worksheet functions with respect to formulas and then design your macro to fit the constraints that are built into the Excel application. I cannot cover it all in this thread. You will need to do some research on your own to develop an understanding of how the applications work. Trial and error is a great teacher.

 

[ipbr21054]

The cells you mention have have one or the other of the trace items.

 

[JLGWhiz]

In this example, Cells A2 and B2 are the precedent cells, B3 is precedent to C4. In order to show C4 as blank and retain the formula, B3 has to be reduced to zero or blank, shince B3 gets it value from A2 and B2 so long as A 2 is greater than B2 then we can simply remove the value from A2 and both B3 and C4 will appear blank but retain the formulas.

TestBase.xlsm
	A	B	C
1
2	5	2
3		10
4			7
5
Sheet1

Cell Formulas
Range	Formula
B3,C4	B3	=IF(A2>B2, A2*B2,"")

With A2 value removed

TestBase.xlsm
	A	B	C	D
1
2		2
3
4
Sheet1

Cell Formulas
Range	Formula
B3,C4	B3	=IF(A2>B2, A2*B2,"")