Sub TrimMyDefinedColumns()
Dim arrColumns()
Dim lngColumn As Long, lngLastRow As Long, lngRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
arrColumns = Array(1, 3, 5, 7, 9, 14)
With Sheet1
For lngColumn = 0 To UBound(arrColumns)
With .Columns(arrColumns(lngColumn))
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For lngRow = 1 To lngLastRow
.Cells(lngRow, 1).Value = Trim(.Cells(lngRow, 1).Value)
Next lngRow
End With
Next lngColumn
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Sub MyTrim()
With ActiveSheet.UsedRange
.Value = Application.Trim(.Value)
End With
End Sub
Very interesting. For that to work across all cells in the range, you must specifically specify Application as the object from which Trim is descended. If you use WorksheetFunction for the object (or even Application.WorksheetFunction) you get a Type Mismatch error. So what does that mean... there are two identically named Excel Trim functions that work differently from each other? Very interesting indeed. I wonder if there are any other "worksheet functions" that work differently when descended from the Application object as opposed to WorksheetFunction object? More importantly, I wonder if there is any documentation describing this different functionality?
