Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    986
    Thanks
    63
    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
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,870
    Thanks
    0
    Thanked 79 Times in 75 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.

  4. #3
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    986
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by rory View Post
    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

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,870
    Thanks
    0
    Thanked 79 Times in 75 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.

  6. #5
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    986
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by rory View Post
    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

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,870
    Thanks
    0
    Thanked 79 Times in 75 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.

  8. #7
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    986
    Thanks
    63
    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

  9. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,870
    Thanks
    0
    Thanked 79 Times in 75 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.

  10. #9
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    986
    Thanks
    63
    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

  11. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,870
    Thanks
    0
    Thanked 79 Times in 75 Posts
    Did you mean 'not works' or 'now works'?
    Regards,
    Rory
    Microsoft MVP - Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •