Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

vba function Trim to remove spaces among numbers



I  need  to  find  out  what's  the  problem  . the  code  should  delete  the  spaces  among  the  number  in  column D   . I  have  some space  for  some  numbers  .  I  need  deleting   the  spaces  to  sum the  values correctly way .

Sub TrimRng()
Dim rng As Range
Set rng = Range("D2:D94")
Application.ScreenUpdating = False
With rng
    .Value = Evaluate(Replace("If(@="""","""",Trim(@))", "@", .Address))
End With
Application.ScreenUpdating = True
End Sub


Selected Answer


I was confused by the action of REPLACE at first then I realised that your spaces are not regular spaces but non-breaking spaces (ASCII character 160).

I've used a loop instead to do what you want (and declared a new variable- see changes in bold below):

Sub TrimRng()
Dim rng As Range, Cl As Range

Set rng = Range("D2:D94")

Application.ScreenUpdating = False

For Each Cl In rng
    Cl.Value = Replace(Cl.Value, Chr(160), "", , , vbTextCompare)
Next Cl

Application.ScreenUpdating = True

End Sub
As an aside, there's no real need to turn Application.ScreenUpdating off and on- Excel should be fast enough in working with just a few cells.

Hope this helps



Hi John,
this  works. I  thought  to  it can  be  possible  without  using  loop, but  sometimes some  cases enforce for using loop  .
much  appreciate !
have  a nice  weekend !
MAKLIL (rep: 24) Nov 19, '21 at 10:53 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login