Replace using Do loop

My text has duplicate characters in the same cell, let's say the
character is "a". I want to find and replace all duplicates until
there is never two a's in a row, so

baaaat
baaat
baat
bat

becomes

bat
bat
bat
bat

Should I use a Do While or Do Until loop for this? As in...

Do
Selection.Replace What:="aa", Replacement:="a", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False
Loop Until [?????]

Seems like using Excel's built-in Replace function would be the
simplest and have the fastest execution. But what should be the
condition which causes the loop to exit? What happens when a Replace
call finds nothing? Thanks.

(P.S. the duplicate characters are not letters, they're stuff like {
and ^, so don't worry about messing up words like aardvark, as the
above example would.)

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 & ReplaceCharacter, _
Replacement:=ReplaceCharacter
Set rngFound = rngToSearch.Find(What:=ReplaceCharacter & _
ReplaceCharacter, LookAt:=xlPart)
Loop
End Sub
--
HTH...

Jim Thomlinson


"Dave B" wrote:

> My text has duplicate characters in the same cell, let's say the
> character is "a". I want to find and replace all duplicates until
> there is never two a's in a row, so
>
> baaaat
> baaat
> baat
> bat
>
> becomes
>
> bat
> bat
> bat
> bat
>
> Should I use a Do While or Do Until loop for this? As in...
>
> Do
> Selection.Replace What:="aa", Replacement:="a", LookAt:=xlPart,
> SearchOrder:=xlByRows, MatchCase:=False
> Loop Until [?????]
>
> Seems like using Excel's built-in Replace function would be the
> simplest and have the fastest execution. But what should be the
> condition which causes the loop to exit? What happens when a Replace
> call finds nothing? Thanks.
>
> (P.S. the duplicate characters are not letters, they're stuff like {
> and ^, so don't worry about messing up words like aardvark, as the
> above example would.)
>
>