Deleting excessive spaces

[Pauljj]

I have up till now been using the code below to delete excessive spaces

Dim r As Range
Dim Target As Range

Set Target = ActiveSheet.Range("C1:C3000")

For Each r In Target
If Len(r.Value) <> Len(Trim(r.Value)) Then
r.Value = Application.Trim(r.Value)
End If
Next


Which seems to have worked well up till now

I have discovered that if I have a phrase in column C for example

testing testing

the code does not pick up on the fact that this has two spaces between both words, I had thought it would delete all excessive spaces, not just what is before and after

Does anyone have any ideas please ?

 

[Sektor]

Sub Excess()

    Dim r As Range, Target As Range
    
    Application.ScreenUpdating = False
    
    Set Target = ActiveSheet.Range("C1:C3000")
    
    For Each r In Target
        r = WorksheetFunction.Trim(r)
    Next

End Sub

 

[MickG]

Try:-

R.value = replace(R.value," ","")

Mick

 

[Pauljj]

Hmmm both of these changes send my code into a never ending loop ?

 

[Sektor]

Is it statement or question?

 

[MickG]

Not sure why!!!
This worked for me.

[COLOR="Navy"]Sub[/COLOR] MG27May15
[COLOR="Navy"]Dim[/COLOR] r [COLOR="Navy"]As[/COLOR] Range, Rng [COLOR="Navy"]As[/COLOR] Range
Application.ScreenUpdating = False
    [COLOR="Navy"]Set[/COLOR] Rng = ActiveSheet.Range("C1:C3000")
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] r [COLOR="Navy"]In[/COLOR] Rng
        r.value = Replace(r.value, " ", "")
        [COLOR="Navy"]Next[/COLOR]
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Richard Schollar]

Perhaps:

Dim r As Range

Set r = Range("C1:C3000")

r.Value = Evaluate("IF(ROW(),TRIM(" & r.Address & "))")

 

[Pauljj]

Thanks Mick and Richard, codes work a treat, thanks for your help