Trim all Cells in a Worksheet - VBA
Hi All,
Here is a VBA routine to Trim as well as Clean all used range in a worksheet.
Code:
'// Developed by Kris @ ExcelFox.com
Sub TRIMALL(ByRef Sht As Worksheet)
Dim aAddr As String
Dim MaxLen As Long
Dim v As Variant
Dim r As Long
Dim c As Long
Dim Rng As Range
Dim a As Long
On Error Resume Next
Set Rng = Sht.UsedRange.SpecialCells(2, 6)
On Error GoTo 0
If Rng Is Nothing Then Exit Sub
For a = 1 To Rng.Areas.Count
With Rng.Areas(a)
aAddr = "'" & Sht.Name & "'!" & .Address
MaxLen = Evaluate("max(len(" & aAddr & "))")
If MaxLen > 255 Then v = .Value
.Value = Evaluate("if(len(" & aAddr & "),clean(trim(" & aAddr & "))," & aAddr & ")")
If MaxLen > 255 Then
If IsArray(v) Then
For r = 1 To UBound(v, 1)
For c = 1 To UBound(v, 2)
If Len(v(r, c)) > 255 Then .Cells(r, c).Value = Application.Clean(Application.Trim(v(r, c)))
Next
Next
Else
.Cells(1).Value = Application.Clean(Application.Trim(v))
End If
End If
End With
Next
End Sub
and call the routine like..
Code:
Sub Test()
TRIMALL ThisWorkbook.Worksheets("Sheet1")
End Sub
Enjoy !!
