Applying the Excel's Trim Function on the Entire Workbook.

[omairhe]

Hey Excel Gurus,

I have a large data with multiple worksheets. Please my requirement this time is for a vba code that performs trimming on the existing data present in all worksheets of my workbook.

For example if text is (without the quotation marks):

" abc def "

the code runs and check for data cells on each sheet and voila...

"abc def"

Thank you and much appreciation.

 

[Fluff]

Try this mod to Yongle' code

Sub Trim_All_Sheets()
    Dim ws As Worksheet, cel As Range
        With Application
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
                For Each ws In ThisWorkbook.Sheets
                    For Each cel In ws.Cells.SpecialCells(xlCellTypeConstants)
                        If Not IsError(cel) Then cel.Value = WorksheetFunction.Trim(cel.Value)
                    Next cel
                Next ws
            .Calculation = xlCalculationAutomatic
    End With
End Sub

 

[Yongle]

@Fluff - good mod
- much preferable to standard lazy On Error Resume Next