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.)
hello
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
Maklil
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 SubHope this helps