You can use the following basic syntax to remove spaces from a string using VBA:
Sub RemoveSpaces()
Dim i As Integer
For i = 2 To 8
Range("B" & i) = Replace(Range("A" & i), " ", "")
Next i
End Sub
This particular example removes the spaces from each string in the range A2:A8 and outputs the results in the range B2:B8.
The following example shows how to use this syntax in practice.
Example: Using VBA to Remove Spaces from Strings
Suppose we have the following list of strings in Excel:
Suppose we would like to remove the spaces from each string.
We can create the following macro to do so:
Sub RemoveSpaces()
Dim i As Integer
For i = 2 To 8
Range("B" & i) = Replace(Range("A" & i), " ", "")
Next i
End Sub
When we run this macro, we receive the following output:
Column B displays each of the strings in column A with the spaces removed.
Note: You can find the complete documentation for the VBA Replace method here.
Additional Resources
The following tutorials explain how to perform other common tasks using VBA:
VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String
VBA: How to Count Cells with Specific Text