Macro to clear data

[HowardC]

I have a spreadsheet that I enter data for cash flow purposes on a daily basis. At the beginning of each month I need to clear out the data containing values only as well as values beginnining with an = for eg 20000+50000+25000+74000 etc, but not formulas and text

I need the macro to clear the values , including data that has been added up as explaimned above from row 9 onwards and from column C

Your assistance will be most appreciated

Regards

Howard

[rory]

Anything that begins with an = sign, is a formula. How would the code distinguish between those and 'regular' formulas?

[HowardC]

Anything that begins with an = sign, is a formula. How would the code distinguish between those and 'regular' formulas?

Hi Rory

Thanks for the reply. Would it not be possible to exclude sum & average after the = sign?

Regards

Howard

[rory]

Yes, but you also have some formulas that are straight cell references, or that add up cells using + rather than SUM. Would it be safe to simply clear any formula cells that don't refer to other cells? Also, I assume this should apply only from C9 downwards (and to the right)?

[HowardC]

Yes, but you also have some formulas that are straight cell references, or that add up cells using + rather than SUM. Would it be safe to simply clear any formula cells that don't refer to other cells? Also, I assume this should apply only from C9 downwards (and to the right)?

Hi Rory

Thanks for the reply. It would be safe to clear any formulas that do not refer to other cells and this will apply from C9 downwards and to the right

Regards

Howard

[rory]

Try this code. Note that it assumes you don't have formulas that refer to other sheets.

Sub ClearOldData()
	Dim rngSearch As Range, rngLastCell As Range, rngCell As Range, rngPrecs As Range
	Dim lngCalc As Long
	Set rngLastCell = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell)
	Set rngSearch = Range("C9", rngLastCell)
	
	' turn off calculation and screen redraw to speed things up
	With Application
 	.ScreenUpdating = False
 	lngCalc = .Calculation
 	.Calculation = xlCalculationManual
	End With
	
	' loop through cells
	For Each rngCell In rngSearch
 	If Not IsEmpty(rngCell) Then
 	' check if cell contains a formula
 	If rngCell.HasFormula Then
 	' check if formula refers to other cells; if not, clear it.
 	' NOTE: this assumes all references are to cells in the same worksheet!
 	On Error Resume Next
 	Set rngPrecs = rngCell.Precedents
 	On Error GoTo 0
 	If rngPrecs Is Nothing Then
 	rngCell.ClearContents
 	Else
 	Set rngPrecs = Nothing
 	End If
 	Else
 	' no formula, so clear it.
 	rngCell.ClearContents
 	End If
 	End If
	Next rngCell
	
	' turn everything back on
	With Application
 	.Calculation = lngCalc
 	.ScreenUpdating = True
	End With

End Sub

[HowardC]

Hi Rory

Thanks for the help.

The code works well, except that it clears text. Please amend your code so that cells containing text are not cleared


Regards


Howard

[rory]

I didn't see any text cells?
Anyway, try this one:

Sub ClearOldData()
 Dim rngSearch As Range, rngLastCell As Range, rngCell As Range, rngPrecs As Range
 Dim lngCalc As Long
 Set rngLastCell = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell)
 Set rngSearch = Range("C9", rngLastCell)
 
 ' turn off calculation and screen redraw to speed things up
 With Application
 .ScreenUpdating = False
 lngCalc = .Calculation
 .Calculation = xlCalculationManual
 End With
 
 ' loop through cells
 For Each rngCell In rngSearch
 If Not IsEmpty(rngCell) and IsNumeric(rngCell.Value) Then
 ' check if cell contains a formula
 If rngCell.HasFormula Then
 ' check if formula refers to other cells; if not, clear it.
 ' NOTE: this assumes all references are to cells in the same worksheet!
 On Error Resume Next
 Set rngPrecs = rngCell.Precedents
 On Error GoTo 0
 If rngPrecs Is Nothing Then
 rngCell.ClearContents
 Else
 Set rngPrecs = Nothing
 End If
 Else
 ' no formula, so clear it.
 rngCell.ClearContents
 End If
 End If
 Next rngCell
 
 ' turn everything back on
 With Application
 .Calculation = lngCalc
 .ScreenUpdating = True
 End With

End Sub

[HowardC]

Hi Rory

Thanks for all the effort in writing the code, which is much appreciated

The code not works perfectly

Regards

Howard

[rory]

Did you mean 'not works' or 'now works'?