Results 1 to 10 of 10
Thread: Macro to clear data

20100104, 22:18 #1
 Join Date
 Feb 2008
 Posts
 1,169
 Thanks
 74
 Thanked 2 Times in 2 Posts
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

20100105, 03:08 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,121
 Thanks
 2
 Thanked 142 Times in 135 Posts
Anything that begins with an = sign, is a formula. How would the code distinguish between those and 'regular' formulas?
Regards,
Rory
Microsoft MVP  Excel

20100105, 03:38 #3
 Join Date
 Feb 2008
 Posts
 1,169
 Thanks
 74
 Thanked 2 Times in 2 Posts

20100105, 04:42 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,121
 Thanks
 2
 Thanked 142 Times in 135 Posts
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)?
Regards,
Rory
Microsoft MVP  Excel

20100105, 05:25 #5
 Join Date
 Feb 2008
 Posts
 1,169
 Thanks
 74
 Thanked 2 Times in 2 Posts

20100105, 06:05 #6
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,121
 Thanks
 2
 Thanked 142 Times in 135 Posts
Try this code. Note that it assumes you don't have formulas that refer to other sheets.
Code: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
Regards,
Rory
Microsoft MVP  Excel

20100105, 07:06 #7
 Join Date
 Feb 2008
 Posts
 1,169
 Thanks
 74
 Thanked 2 Times in 2 Posts
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

20100105, 07:10 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,121
 Thanks
 2
 Thanked 142 Times in 135 Posts
I didn't see any text cells?
Anyway, try this one:
Code: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
Regards,
Rory
Microsoft MVP  Excel

20100105, 10:33 #9
 Join Date
 Feb 2008
 Posts
 1,169
 Thanks
 74
 Thanked 2 Times in 2 Posts
Hi Rory
Thanks for all the effort in writing the code, which is much appreciated
The code not works perfectly
Regards
Howard

20100105, 10:49 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,121
 Thanks
 2
 Thanked 142 Times in 135 Posts
Did you mean 'not works' or 'now works'?
Regards,
Rory
Microsoft MVP  Excel