Trim all Cells in a Worksheet - VBA

[Admin]

Hi All,

Here is a VBA routine to Trim as well as Clean all used range in a worksheet.

'// 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..

Sub Test()

    TRIMALL ThisWorkbook.Worksheets("Sheet1")

End Sub

Enjoy !!