CODEWITHSUNDEEP
excelvba
10101
10101

Reputation: 2412

Loop and replace two or more spaces with only one

I want to replace all multiple spaces in cells in my range A1:K1000. So if 2 or more spaces, then replace them with one space " ".

My current code replaces spaces that are " " (2) spaces. How to adjust it to work with 2 or more spaces? So it will handle cases like " ", " ", " " etc.

Here is my code:

Sub RemoveSpaces()

Dim r1 As Range
Set r1 = ActiveSheet.Range("A1:K1000") 
r1.Replace _
      What:=Space(2), _
      Replacement:=" ", _
      SearchOrder:=xlByColumns, _
      MatchCase:=True
Set r1 = r1.Find(What:=Space(2))
If Not r1 Is Nothing Then
   Call RemoveSpaces
End If
End Sub

Upvotes: 1

Views: 297

Answers (2)

JvdV
JvdV

Reputation: 75910

There is no need to loop cells:

enter image description here

Sub Test()

Dim rng As Range
Set rng = Sheets("Sheet1").Range("A1:A3")
rng.Value = Application.Trim(rng)

End Sub

enter image description here

Upvotes: 3

10101
10101

Reputation: 2412

So from Scott Craner comment, solution to this question seems to be:

Sub RemoveSpaces()

    Dim A As Range

    Set A = ActiveSheet.Range("A1:K1000") 

    For Each cell In A
    cell.Value = WorksheetFunction.Trim(cell)
    Next

End Sub

Upvotes: 0

Related Questions