Solved: Delete any spaces in cell

[Hoopsah]

Hi

does anyone know of a quick macro that will check each cell for blank spaces and delete them?

Thanks for any help

[JKwan]

Try this - select a range and run
[vba]
Public Function RemoveSpaces(strInput As String)
' Removes all spaces from a string of text
' This can also remove any character from a string
' Replace the " " with whatever character you want
' IE - "a" will remove all of the a's from a string
test:
If InStr(strInput, " ") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, " ") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, " "))
GoTo test
End If
End Function


Sub DeleteSpaces()
Application.ScreenUpdating = False
On Error Resume Next
Dim objCell As Object
For Each objCell In Selection.Cells
objCell.Value = RemoveSpaces(objCell.Value)
Next objCell
Application.ScreenUpdating = True
End Sub
[/vba]

[Hoopsah]

Fantastic JKwan - works a treat.

Thanks for your help!

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

.Cells(i, TEST_COLUMN).Value = Replace(.Cells(i, TEST_COLUMN).Value, " ", "")
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]