Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to clear Data including additions, but not sum formulas

    I would like VBA code to clear all data in Col d, formulas such as formula such as =sum(

    However where data has been added together for eg = 201365+35782+74568 etc, these must also be cleared

    Your assistance in this regard will be most appreciated

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Howard,

    I'm a little confused. You said "All Data" but then felt you need clarification. To me "All Data" means everything, e.g. wipe out column D. Is this what you want?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to clear Data including additions, but not sum formulas

    Thanks for the reply. I would like all the values, including values added for eg. = 25000+28350-14250 to be cleared from D9 onwards. The only data that must not be cleared are actual formula's for eg =sum

    In the attached example D9, D16 D31:36, D41:45,D5465 must be cleared as these are values (D15 contains =264026+122880 which must also be cleared)

    Your assistance will be most appreciated
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Howard

    In your example file, the thing that distinguishes which cells you want to clear is that the second character in the cell is numeric. For example, in the formula cells you wish to retain e.g. =SUM.. ; =D46 ; =D51 etc, the second character is NOT numeric. In the cells you wish to clear, e.g. 162254 ; =264026+122880 ; 41293.16 etc, the second character IS numeric.
    So you could use this:
    Sub clearColDnumbers()

    For Each cell In [d9:d65]
    If cell Like "?#*" Then
    cell.ClearContents
    End If
    Next

    End Sub

    zeddy

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the help, much appreciated

    Regards

    Howard

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    so you tried it then??

    zeddy

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    I tried your macro and it works perfectly. I have another spreadsheet that where I need all the values cleared including where values are added = 25000+36125-18125 (simillar to my previous example where you provided me with the code) , except formulas for eg =sum(A1:S100). The range is B1 to B50. Please also ensure that text items are not cleared

    Regards

    Howard

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Howard

    A little explanation for anyone reading this that isn't familiar with the Like operator:
    If cell Like "?#*"
    The ? means that the first character can be anything, i.e. numeric or non-numeric. This takes care of cells like = 25000+36125-18125 and cells like 162254
    The # means that the second character must be a digit, so cells like =SUM(... will be excluded.
    The * means I don't care what comes after that.

    So text cells, like Fred, Total etc will also be excluded from the ClearContents command.
    To change the range, simply change [d9:d69] to [b1:b50] as required.

    So we end up with..
    Sub clearColBnumbers()

    For Each cell In [b1:b50]
    If cell Like "?#*" Then
    cell.ClearContents
    End If
    Next

    End Sub

    zeddy

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the explanation & your help. It would be appreciated if you would amend you code so that the code will clear the applicable data on all sheets in the workbook

    Regards

    Howard

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Howard

    You can repeat the process for all worksheets by including a loop in the VBA code to do that.
    The loop should select each worksheet, but ignore any chart sheets etc that may be there, (so we use ThisWorkbook.Worksheets rather than ThisWorkbook.Sheets in the code).
    Also, we may need to adjust the range to be processed to be sure it covers all the sheets to be processed.
    So, for example, we could use something like [b1:d69] as the range to be processed, covering columns [B], [C] and [D], rows 1 to 69. Adjust accordingly.
    The other thing to be aware of is that if one of the cells in the range to be processed is a formula, and the formula shows a #VALUE! or other error, then this cell 'value' will trigger an error when we try to test the cell's contents.
    So we need to skip round any such cells by using an 'on error resume next' command in the code.
    Finally, as we process each sheet it would probably be nice to return to the sheet we started on.
    So here is the code to do that:
    Code:
    Sub clearNumbersInAllSheets()
    zStartSheet = ActiveSheet.Name              'save name for later use
    On Error Resume Next                        'ignore any cell errors
    For Each zSht In ThisWorkbook.Worksheets    'loop through all worksheets (no charts)
    zSht.Select                                 'switch to worksheet for processing
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each cell In [b1:d65]                   'loop through all cells in THIS range
     If cell Like "?#*" Then                    'check first two characters
     cell.ClearContents                         'clear cells that match the condition
     End If                                     'end of test
    Next                                        'process next cell
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Next zSht                                   'process next worksheet
    Worksheets(zStartSheet).Select              'return to first sheet when finished
    On Error GoTo 0                             'reset error trap (just to be tidy)
     
    End Sub
    zeddy

  11. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the code as well as the explanations. This is much appreciated

    Regards

    Howard

Posting Permissions

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