Remove Special Character with the help of VBA Code

[Basavaraj K H]

Dear Team
i got the below mentioned code from Google but from that code i can able remove all special character with full spaces but i don't want to remove single space. kindly modify the below code.

Sub test()
Application.ScreenUpdating = False
Dim rng As Range
Dim strg$

i& = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For Each rng In Sheet1.Range("A1:A" & i).Cells
  For j& = 1 To Len(rng)
  Select Case Asc(Mid(rng, j, 1))
  Case 48 To 57, 65 To 90, 97 To 122:
  strg = strg & Mid(rng, j, 1)
  Sheet1.Cells(rng.Row, rng.Column + 1) = strg
  End Select
  Next
  strg = vbNullString
Next
Application.ScreenUpdating = True
End Sub

Thanks

Regards
Basavaraj K H
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Marc L]

Hi !

TRIM Excel worksheet function keeps a space between words
(to see in Excel help like CLEAN as well) …

In VBA use it that way : Application.Trim({Text})

{Text} is a text string between double quotes or a variable …
​