CODEWITHSUNDEEP
excelvba
VBAlearner
VBAlearner

Reputation: 9

Replace not functioning

I am trying simple replace to delete the brackets and text within it but it's not working. Please help. My code is:

Dim r As Integer
For r = 2 To 30
   Cells(r, 3) = Replace(Cells(r, 3), "(*)", "")
   MsgBox Cells(r, 3)
Next

Upvotes: 0

Views: 63

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Use Range.Replace function and make the replacement at once:

Sub testReplace()
  Dim rngR As Range
  Set rngR = Range("C2:C30")
   rngR.Replace "(*)", ""
End Sub

Upvotes: 1

Spencer Barnes
Spencer Barnes

Reputation: 2877

The Ordinary replace function doesn't take wildcards * - but for some reason, Range.Replace does.

Dim rng1 as Range

    Set rng1 = Range(Cells(2,3), Cells(30,3))
    rng1.Replace What:= "(*)", Replacement:= "", LookAt:= xlPart, SearchOrder:=xlByRows, MatchCase:= False, SearchFormat:=False, ReplaceFormat:=False

(Obviously you probably won't need to use all of those named parameters...)

Upvotes: 1

Related Questions