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.

 

[jmacleary]

Here you are.

Sub trimmer()
      Dim Current As Worksheet

' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets
For Each cell In Current.UsedRange.Cells
    cell.Value = Trim(cell.Value)
Next cell
Next Current
End Sub

 

[Yongle]

Try this

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)
                        cel.Value = WorksheetFunction.Trim(cel.Value)
                    Next cel
                Next ws
            .Calculation = xlCalculationAutomatic
    End With
End Sub

 

[Yongle]

Just spotted reply from @jmacleary
- be aware of the difference between the 2 solutions, both of which are equally valid but do different things
- choose the one that does what you want
@Yongle solution replaces ONLY those cells with constant values (ie it ignores formulas)
@jmacleary solution replaces each cell value with trimmed value and removes formulas from all cells

 

[jmacleary]

Well done Yongle. It was careless of me to overlook the effect on formulas. I also like that you turn off screen updating and calculation to speed it up.

Your solution just needs .ScreenUpdating=True adding after the line: .Calculation = xlCalculationAutomatic

 

[omairhe]

I am getting a type mismatch error for both the codes and the values do not trim.

 

[Fluff]

@jmacleary
You do not need to turn ScreenUpdating back on at the end of the code, as it will happen automatically.
@omairhe
Which lines give the error?

 

[omairhe]

@jmacleary
You do not need to turn ScreenUpdating back on at the end of the code, as it will happen automatically.
@omairhe
Which lines give the error?

Runtime error 13 on the following lines

    cell.Value = Trim(cell.Value)

and

                        cel.Value = WorksheetFunction.Trim(cel.Value)

should I test it on a new blank workbook?

 

[Fluff]

Do any of your cells contain error values such as #N/A, #NAME? etc

 

[omairhe]

Do any of your cells contain error values such as #N/A, #NA ME? etc

Yes I have #N/A as text values in my original workbook.tested it with and without the n/a on a blank workbook and the problem seems to be gone.

Can the code be altered to make it bypass this situation. Thank you.