Macro to clear Data including additions, but not sum formulas

[HowardC]

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

[RetiredGeek]

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?

[HowardC]

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

[zeddy]

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

[HowardC]

Hi Zeddy

Thanks for the help, much appreciated

Regards

Howard

[zeddy]

so you tried it then??

zeddy

[HowardC]

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

[zeddy]

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

[HowardC]

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

[zeddy]

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:

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

[HowardC]

Hi Zeddy

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

Regards

Howard