wildcards in replace

I am trying to get rid of all cells that contain multiple ='s. For
instance in A13 it may read ===== === =========== =======. Then
in A40, it may read = ====== = ====. Basically these lines are
stopping my trim statements from running. I already have a line that
deletes all empty rows so it would be realy great if I could just get
rid of the characters all together and then delete teh row with that
line.

Give this a try...

Public Sub test()
Call RemoveDuplicates("=")
End Sub

Public Sub RemoveDuplicates(ByVal ReplaceCharacter As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Cells
Set rngFound = rngToSearch.Find(What:=ReplaceCharacter & _
ReplaceCharacter, LookAt:=xlPart)

Do While Not rngFound Is Nothing
rngToSearch.Replace What:=ReplaceCharacter, _
Replacement:=""
Set rngFound = rngToSearch.Find(What:=ReplaceCharacter & _
ReplaceCharacter, LookAt:=xlPart)
Loop
End Sub
--
HTH...

Jim Thomlinson


"[email protected]" wrote:

> I am trying to get rid of all cells that contain multiple ='s. For
> instance in A13 it may read ===== === =========== =======. Then
> in A40, it may read = ====== = ====. Basically these lines are
> stopping my trim statements from running. I already have a line that
> deletes all empty rows so it would be realy great if I could just get
> rid of the characters all together and then delete teh row with that
> line.
>
>

Sorry htat was adapted from some old code that I had that needs a little
tweaking... Try this...

Public Sub test()
Call RemoveDuplicates("=")
End Sub

Public Sub RemoveDuplicates(ByVal ReplaceCharacter As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Cells.SpecialCells(xlCellTypeConstants)
Set rngFound = rngToSearch.Find(What:=ReplaceCharacter, LookAt:=xlPart)

Do While Not rngFound Is Nothing
rngToSearch.Replace What:=ReplaceCharacter, _
Replacement:=""
Set rngFound = rngToSearch.Find(What:=ReplaceCharacter,
LookAt:=xlPart)
Loop
End Sub

That gets rid of the ='s. Did you still need some help on the deleting rows?
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> Give this a try...
>
> Public Sub test()
> Call RemoveDuplicates("=")
> End Sub
>
> Public Sub RemoveDuplicates(ByVal ReplaceCharacter As String)
> Dim wks As Worksheet
> Dim rngToSearch As Range
> Dim rngFound As Range
>
> Set wks = Sheets("Sheet1")
> Set rngToSearch = wks.Cells
> Set rngFound = rngToSearch.Find(What:=ReplaceCharacter & _
> ReplaceCharacter, LookAt:=xlPart)
>
> Do While Not rngFound Is Nothing
> rngToSearch.Replace What:=ReplaceCharacter, _
> Replacement:=""
> Set rngFound = rngToSearch.Find(What:=ReplaceCharacter & _
> ReplaceCharacter, LookAt:=xlPart)
> Loop
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "[email protected]" wrote:
>
> > I am trying to get rid of all cells that contain multiple ='s. For
> > instance in A13 it may read ===== === =========== =======. Then
> > in A40, it may read = ====== = ====. Basically these lines are
> > stopping my trim statements from running. I already have a line that
> > deletes all empty rows so it would be realy great if I could just get
> > rid of the characters all together and then delete teh row with that
> > line.
> >
> >

Your a genius...thanks...worked great.