If you want to trim selected cells in one go . Try this macro
Sub trim_selected_range()
Dim cell_1 As Range
For Each cell_1 In Selection.Cells
If cell_1.HasFormula = False And Not IsEmpty(cell_1) And Not IsDate(cell_1) Then
cell_1.Value = Application.WorksheetFunction.Trim(cell_1.Value)
End If
Next
End Sub
Steps to Use
Sub trim_selected_range()
Dim cell_1 As Range
For Each cell_1 In Selection.Cells
If cell_1.HasFormula = False And Not IsEmpty(cell_1) And Not IsDate(cell_1) Then
cell_1.Value = Application.WorksheetFunction.Trim(cell_1.Value)
End If
Next
End Sub
Steps to Use
- Copy the below code
- Press Alt+F11 to open VBA editor
- Paste it in any public module or module 1
- Select the cells & run the Macro
I have been playing with routine and it is very useful, especially with imported data. It can also be used to capitalise lists (names for example) or to convert numbers to currency. Simply replace Function.Trim with a different function e.g. Function.Proper (for capitalisation) or Function.Dollar for currency.
ReplyDeleteThere are several other functions that could easily be used; Clean, Dollar, Fixed, Proper, and Round. IsError type functions would also work, as would find and replace type functions.
You don't need this: Application.WorksheetFunction. as Trim is also a VBA function. No need to call the Applications worksheet level function :). I would probably also use .formula and .text instead of .value to stop it from resolving textual numbers (ie UPC's and catalogue codes)
ReplyDelete