Deleting All Cells with Number formulas Ex. =2+3 not = A1+B2 - 0

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Deleting All Cells with formulas with Numbers Ex. =2+3 not = A1+B2

I am trying to create a copy of workbook with only formulas, but some cells
exist with =2+3 which I do Not wanted. How can I delete all of this in a
single click
(Not Individualy)
 
this should do it

Sub cleanformulas()
For Each c In Selection
If c.HasFormula And _
IsNumeric(Mid(c.Formula, 2, 1)) Then c.Clear
Next
End Sub
 
Deleting All Cells with formulas with Numbers Ex. =2+3 not = A1+B2

This idea looks to see if there are any Precedents within the formula.

Sub Demo()
'// Dana DeLouis
Dim BigRng As Range
Dim Rng As Range

On Error Resume Next
Set BigRng = Cells.SpecialCells(xlCellTypeFormulas)
If BigRng Is Nothing Then Exit Sub

For Each Rng In BigRng.Cells
If Rng.Precedents.Count > 0 Then
If Err.Number = 1004 Then 'No cells were found.
Rng.Clear
Err.Clear
End If
End If
Next Rng
ActiveSheet.UsedRange
End Sub

HTH :>)
 
Dana DeLouis said:
This idea looks to see if there are any Precedents within the formula.

Sub Demo() ....
If Rng.Precedents.Count > 0 Then
If Err.Number = 1004 Then 'No cells were found.
Rng.Clear
Err.Clear
End If
End If
Click to expand...
....

OP's specs may be incomplete. Should cells containing formulas like =NOW()
or =RAND() be deleted? Your macro deletes them. It also deletes DDE formulas
and cells containing external references into closed workbooks when there
are no references to ranges in the formula's parent workbook.

If only cells containing formulas involving only operations with constant
operands should be deleted, then maybe something like


Sub foo()
Const RE_DQS As String = """[^""]*(""""[^""]*)*"""
Const RE_TKN As String = "\b[_A-Za-z]"

Dim rng As Range, r As Range, ws As Worksheet
Dim re As Object, rf As String

Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.IgnoreCase = False

For Each ws In ActiveWorkbook.Worksheets
On Error GoTo Continue
Set rng = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

For Each r In rng
rf = r.Formula
re.Pattern = RE_DQS
rf = re.Replace(rf, """""") 'reduce all string constants
re.Pattern = RE_TKN
If Not re.Test(rf) Then r.Clear

Next r

Continue:
Err.Clear

Next ws

End Sub
 
... =NOW() or =RAND() .....Your macro deletes them.

Thanks Harlan. I never even thought about formulas with no arguments. Good
catch. I like your RegExp formula. :>)

I was going to suggest a "RegExp" using .Matches over in the thread 'Find
nth instance of a character in a string', but I don't think it could have
beaten your time.

--
Dana DeLouis
Win XP & Office 2003


Harlan Grove said:
Dana DeLouis said:
This idea looks to see if there are any Precedents within the formula.

Sub Demo() ...
If Rng.Precedents.Count > 0 Then
If Err.Number = 1004 Then 'No cells were found.
Rng.Clear
Err.Clear
End If
End If
Click to expand...
...

OP's specs may be incomplete. Should cells containing formulas like =NOW()
or =RAND() be deleted? Your macro deletes them. It also deletes DDE
formulas
and cells containing external references into closed workbooks when there
are no references to ranges in the formula's parent workbook.

If only cells containing formulas involving only operations with constant
operands should be deleted, then maybe something like


Sub foo()
Const RE_DQS As String = """[^""]*(""""[^""]*)*"""
Const RE_TKN As String = "\b[_A-Za-z]"

Dim rng As Range, r As Range, ws As Worksheet
Dim re As Object, rf As String

Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.IgnoreCase = False

For Each ws In ActiveWorkbook.Worksheets
On Error GoTo Continue
Set rng = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

For Each r In rng
rf = r.Formula
re.Pattern = RE_DQS
rf = re.Replace(rf, """""") 'reduce all string constants
re.Pattern = RE_TKN
If Not re.Test(rf) Then r.Clear

Next r

Continue:
Err.Clear

Next ws

End Sub
Click to expand...
 
Back
Top