Table of Contents
A macro to remove all spaces from a string would involve looping through each character of the string and checking if it is a space. If it is a space, it would be removed and replaced with an empty character. The loop would continue until all spaces have been removed from the string.
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 .
The following tutorials explain how to perform other common tasks using VBA: