Trim Your Selected Data

Posted on by

Do you find yourself having to apply Excel’s TRIM function to columns of data from another data source?  The following macro will trim your selected data, i.e. remove blanks from the start or end of each cell. This method can also be adapted to other situations like formatting text or performing calculations.

TRIM Your Selected Data Macro

Place the following code onto a module sheet of your Excel file in the Visual Basic Editor (Alt + F11):

Sub TrimYourSelectedData()
' Trims each cell in the selected data
Dim cell As Range
For Each cell In Selection
 cell.Value = Application.WorksheetFunction.Trim(cell.Value)
Next cell
End Sub

How to Use the Macro

To use it, simply select your cells and then run the macro.

You can make it easier to run your macro by assigning your macro to a shortcut key, e.g. Ctrl + Shift + E. To do this, press Alt + F8 to bring up the Macro dialog box, click Options and then enter your shortcut key in the Shortcut Key field. For example, press Shift + E to use Ctrl + Shift + E – the Ctrl key at the start is compulsory and does not need to be entered.

How Does the Macro Work?

The cells on your Excel sheet that you select are automatically loaded into a Range object called Selection. Using a For Each … Next loop, the macro loops through each cell that you selected.  In each loop it applies Excel’s TRIM function to the current cell.

Change it to a New Situation

If you want to adapt the macro to perform a different action on each selected cell then just change the macro code within the For Each … Next loop.  (You will probably want to change the name of the sub on the first line also.)

For example, you may want to centre your text and make it bold.  Use the macro recorder to help you get the right syntax but adapt it so that it refers to the range object cell.

Here is my code when I used the macro recorder to make my selection bold and centred horizontally:

Sub Macro1()
'
' Macro1 Macro

 Selection.Font.Bold = True
 With Selection
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlBottom
 .WrapText = False
 .Orientation = 0
 .AddIndent = False
 .IndentLevel = 0
 .ShrinkToFit = False
 .ReadingOrder = xlContext
 .MergeCells = False
 End With
End Sub

The key parts of the code that I am interested in are:

Selection.Font.Bold = True
Selection.HorizontalAlignment = xlCenter

Now adapt it to use Cell instead of Selection, i.e. replace Selection with Cell:

Cell.Font.Bold = True
Cell.HorizontalAlignment = xlCenter

Now simply copy this into the middle part of the loop:

For Each cell In Selection
 Cell.Font.Bold = True
 Cell.HorizontalAlignment = xlCenter
Next cell

Try this out for yourself on a common task that you perform on cells, whether that be formatting or a calculation.