Macro 49: Trim Spaces from All Cells in a Range
A frequent problem when you import dates from other sources is leading or trailing spaces. That is,
the values that are imported have spaces at the beginning or end of the cell. This obviously makes it
difficult to do things like VLOOKUP or sorting. Here is a macro that makes it easy to search for and
remove extra spaces in your cells.
How it works
In this macro, we enumerate through a target range, passing each cell in that range through the
Trim function.
Sub Macro49()
‘Step 1:Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells?
Select Case MsgBox(“Can’t Undo this action. ” & _
“Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
‘Step 3: Define the target Range.
Set MyRange = Selection
‘Step 4: Start looping through the range.
For Each MyCell In MyRange
‘Step 5: Trim the Spaces.
If Not IsEmpty(MyCell) Then
MyCell = Trim(MyCell)
End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target
range, and the other called MyCell to hold each cell in the range as the macro enumerates
through them one by one.
2. When you run a macro, it destroys the undo stack. You can’t undo the changes a macro
makes. Because we are actually changing data, we need to give ourselves the option of sav-
ing the workbook before running the macro. Step 2 does this. Here, we call up a message
box that asks if we want to save the workbook first. It then gives us three choices: Yes, No,
and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking Cancel
exits the procedure without running the macro. Clicking No runs the macro without saving
the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we are using the
selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target
range is a named range, you can simply enter its name — Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell as we go
through.
5. After a cell is activated, the macro uses the IsEmpty function to make sure the cell is not
empty. We do this to save a little on performance by skipping the cell if there is nothing in it.
We then pass the value of that cell to the Trim function. The Trim function is a native Excel
function that removes leading and trailing spaces.
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the
macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
