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.)

Replace using Do loop
 

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.)