Trimming whitespace in Excel cells
It's faster to just trim a value than it is to try and figure out if it needs trimming in the first place.

However, that's not where your performance bottleneck is.



My personal #1 rule of fast spreadsheet manipulations:

Thou shalt not directly manipulate data in worksheets

By this I mean, doing anything in a worksheet has huge computational overhead. In the VBA object heirarchy, worksheets are only 2 steps removed from the application object itself. There are layers upon layers of abstractions, events, handlers, objects (not to mention several Billion range objects) buried in a worksheet object, and any time you do something in it, it will trigger a cascade of operations to make sure that nothing in your worksheet gets messed up.

Your sub might run fine for a couple of hundred cells in a small worksheet, but a few thousand cells in a larger spreadsheet might suddenly become unbearably slow.

For this reason, you should interact with worksheets as infrequently as possible. If there is data in your worksheet that you need to analyse: access the worksheet once to read the data into an Array, then do all your computations on the Array, then access the worksheet once to read the data back.

Your sub then looks like this:

Public Sub TrimWorksheetCellValues(Optional ByRef targetSheet As Worksheet)

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    On Error GoTo errorHandler

    If targetSheet Is Nothing Then Set targetSheet = ActiveSheet '/ If no sheet is provided, assume the active sheet

    Dim firstRow As Long, lastRow As Long
    firstRow = 1
    lastRow = targetSheet.Cells.Find(What:="*", _
                         SearchOrder:=xlByRows, _
                         SearchDirection:=xlPrevious).row

    Dim firstColumn As Long, lastColumn As Long
    firstColumn = 1
    lastColumn = targetSheet.Cells(lastRow, Columns.Count).End(xlToLeft).column

    Dim sheetRange As Range
    Set sheetRange = targetSheet.Range(Cells(firstRow, firstColumn), Cells(lastRow, lastColumn))

    Dim sheetArray As Variant
    sheetArray = Array()
    sheetArray = sheetRange


    Dim ix As Long, iy As Long
    Dim elementValue As Variant, trimmedValue As Variant

    For ix = firstRow To lastRow
        For iy = firstColumn To lastColumn
            elementValue = sheetArray(ix, iy)
            trimmedValue = Trim(elementValue)
            sheetArray(ix, iy) = trimmedValue
        Next iy
    Next ix

    sheetRange = sheetArray

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

    Exit Sub

errorHandler:
    MsgBox "Error has occurred.", _
      vbCritical, "Error!"

End Sub




A few other things I modified while I was there:



Function names should be written in PascalCase with the leading word capitalised



Things should be explicitly scoped. Your sub was implicitly targeting the active sheet everywhere but nowhere was that explicitly declared or confirmed as the desired functionality. The sub now explicitly targets a particular sheet or explicitly targets the active sheet if no target is provided.



The firstRow and firstColumn are now explicitly declared and assigned, rather than magically appearing halfway through your code.



Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


These are the standard application settings to turn off if you want your macros to run faster. You should make sure any error handling resets them to their previous values in case your procedure terminates unexpectedly.



Don't catch errors if you're not going to handle them properly. This:

errorHandler:
    MsgBox "Error has occurred.", _
      vbCritical, "Error!"


Is just an error message, but with the useful information (error#, warning etc.) taken out. Do something useful with it (like resetting the application settings) or remove it.