Faster way to find and replace?
 

I have a single column of IF formulas in a named range ("vbDelete").
If the formula evaluates to -1, then my code will clear the cell. I
repurposed some code I found below. Is there a faster/better way to do
this find/replace? Having a difficult time getting .REPLACE to work
instead----can't get it to work on the formula result. It's finding
the string within the formula and removing it from the formula. Anyway
this is my workaround.
vbDelete contains cells with one formula:

(Column B, vbDelete range)
=IF(A1="SomeValue","Don't Delete", -1)

Sub ClearNegatives()
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim wks As Worksheet
Set wks = ActiveSheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set rngToSearch = Range("vbDelete")

For Each rngCurrent In rngToSearch

If rngCurrent.Value = -1 Then _
rngCurrent.ClearContents

Next rngCurrent

Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

end sub


The above isn't too slow. Just feel like there's a more efficient
approach. Any pointers are appreciated!

PS, this is what I tried to get to work, but it keeps replacing within
the formula and not the formula result:
.Replace what:="DELETE_THIS_CELL", replacement:="", lookat:=xlPart,
searchorder:=xlByRows

"DELETE_THIS_CELL" is replaced by the -1 in ClearNegatives.
The .REPLACE results in:
=IF(A1="SomeValue","Don't Delete", "")

Notice the result is a (changed) formula instead of the cell becoming
blank, which is desired.

Faster way to find and replace?
 

Sub clearminuscells()
With Columns("b")
.AutoFilter Field:=1, Criteria1:="-1"
.ClearContents
.AutoFilter
End With
End Sub



On Jan 28, 2:43*am, John wrote:
I have a single column of IF formulas in a named range ("vbDelete").
If the formula evaluates to -1, then my code will clear the cell. I
repurposed some code I found below. Is there a faster/better way to do
this find/replace? Having a difficult time getting .REPLACE to work
instead----can't get it to work on the formula result. It's finding
the string within the formula and removing it from the formula. Anyway
this is my workaround.
vbDelete contains cells with one formula:

(Column B, vbDelete range)
=IF(A1="SomeValue","Don't Delete", -1)

Sub ClearNegatives()
* * Dim rngToSearch As Range
* * Dim rngCurrent As Range
* * Dim wks As Worksheet
* * Set wks = ActiveSheet

* * Application.ScreenUpdating = False
* * Application.Calculation = xlCalculationManual

* * Set rngToSearch = Range("vbDelete")

* * For Each rngCurrent In rngToSearch

* * * * If rngCurrent.Value = -1 Then _
* * * * *rngCurrent.ClearContents

* * Next rngCurrent

* * Application.ScreenUpdating = False
* * Application.Calculation = xlCalculationAutomatic

end sub

The above isn't too slow. Just feel like there's a more efficient
approach. Any pointers are appreciated!

PS, this is what I tried to get to work, but it keeps replacing within
the formula and not the formula result:
*.Replace what:="DELETE_THIS_CELL", replacement:="", lookat:=xlPart,
searchorder:=xlByRows

"DELETE_THIS_CELL" is replaced by the -1 in ClearNegatives.
The .REPLACE results in:
=IF(A1="SomeValue","Don't Delete", "")

Notice the result is a (changed) formula instead of the cell becoming
blank, which is desired.