Trim all Cells in a Worksheet - VBA

**Admin** · 09-14-2011, 09:53 PM

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 !!

**Rick Rothstein** · 02-28-2012, 10:19 PM

Here is a UDF (user defined function) that I developed which will clean and trim the text passed into it. The trim operation is identical to Excel's worksheet TRIM function; however, the clean is slightly different. It cleans some additional non-printing characters that Excel's CLEAN function does not handle. Those additional characters are delineated here...

Remove spaces and nonprinting characters from text - Support - Office.com

I also included an optional argument to convert non-breaking spaces (ASCII 160) to real spaces (ASCII 32). Because non-breaking spaces are such a problem when copying text from the web, I defaulted this optional argument to True (meaning non-breaking space will be converted into true spaces and then handled, along with existing spaces, by the trim operation).

Code:

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim = WorksheetFunction.Trim(S)
End Function

For those reading this thread who are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CleanTrim just like it was a built-in Excel function. For example,

=CleanTrim(A1)

**Admin** · 02-29-2012, 09:57 AM

Hi Rick,

**roberto21** · 01-14-2014, 09:35 PM

Originally Posted by Rick Rothstein

Here is a UDF (user defined function) that I developed which will clean and trim the text passed into it. The trim operation is identical to Excel's worksheet TRIM function; however, the clean is slightly different. It cleans some additional non-printing characters that Excel's CLEAN function does not handle. Those additional characters are delineated here...

Remove spaces and nonprinting characters from text - Support - Office.com

I also included an optional argument to convert non-breaking spaces (ASCII 160) to real spaces (ASCII 32). Because non-breaking spaces are such a problem when copying text from the web, I defaulted this optional argument to True (meaning non-breaking space will be converted into true spaces and then handled, along with existing spaces, by the trim operation).

Code:

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim = WorksheetFunction.Trim(S)
End Function

For those reading this thread who are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CleanTrim just like it was a built-in Excel function. For example,

=CleanTrim(A1)

I managed to get a character (no merit in this) that your function does not eliminate, and I would really understand what it is, because it is driving me nuts. After some experimenting, I can say that:
1) the cell IS cleared simply by canceling it (delete key on the keyboard) or by the equivalent VBA ClearContents method, but NOT by Clean or Trim methods or functions
2) the cell IS counted by COUNTA function (so there is something in it...or there isn't?)
3) the cell IS counted by COUNTBLANK function (so there is nothing in there...)
4) LEN function applied to the cell gives 0 (zero) result

I don't know if I can upload a small workbook with this cell in it, if so please explain. This ghost keep appearing in a database I am working with for a friend, and disrupts a number of pivot tables used for statistics. By the way, the pivot tables count these cells as non empty, whereas if you use the simple excel filter for non-blank function (the one on top of the column) these cells are correctly not counted.
Cheers
Robert, Italy