Trimming cells, before and after

As an experiment, I decided to rewrite one of my old routines that I use a lot, but hadn't re-thought in a while — a simple sub that trims worksheet cells. I plan to do that more often in the coming weeks, because a lot of my code is old and written quick and dirty to get something done, sometimes by brute force.

I get a lot of poorly formatted worksheets, and as you know, if a cell contains hidden spaces at the end, then string comparisons (i.e. "=IF(A1=B1)") will fail. This is maddening at times, so originally I wrote this routine:

Sub Trim_Cells()
Dim cell As Excel.Range

If Selection.Cells.Count > 1 Then
    Selection.SpecialCells(xlCellTypeConstants).Select
End If

With WorksheetFunction
    For Each cell In Selection
        cell = Trim(cell)
        cell = .Trim(cell)
    Next cell
End With

ActiveCell.Select
End Sub

With a shortcut key or toolbar button, it's very convenient. And as you might be able to tell by looking at it, very slow. To speed it up somewhat, it limits the action cells to just constants, but the VBA code still has to hit the worksheet in a 1:2 ratio (!) for each cell in the selection, because for good measure I called the VBA Trim function as well as the Worksheetfunction version. That means each cell has to be hit twice!

I inserted the code above into a generic Timer sub I use to for testing purposes, and ran it on a range of 500 cells. See below for test results.

Sub TimerTest()
'
' place your test code between the starttime and endtime variables
'
StartTime = Timer
'
Dim cell As Excel.Range

If Selection.Cells.Count > 1 Then
    Selection.SpecialCells(xlCellTypeConstants).Select
End If

With WorksheetFunction
    For Each cell In Selection
        cell = Trim(cell)
        cell = .Trim(cell)
    Next cell
End With

ActiveCell.Select
'
EndTime = Timer
MsgBox ("This routine took " & (EndTime - StartTime) & " seconds.")
'
'
'
End Sub

As you can see, this is barely tolerable. In a large worksheet, unacceptable. So on a lark I rewrote the code to use arrays exclusively. Here it is:

Sub Trim_Cells_Array_Method()

Dim arrData() As Variant
Dim arrReturnData() As Variant
Dim rng As Excel.Range
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long

lRows = Selection.Rows.Count
lCols = Selection.Columns.Count

ReDim arrData(1 To lRows, 1 To lCols)
ReDim arrReturnData(1 To lRows, 1 To lCols)

Set rng = Selection
arrData = rng.Value

For j = 1 To lCols
  For i = 1 To lRows
    arrReturnData(i, j) = Trim(arrData(i, j))
  Next i
Next j

rng.Value = arrReturnData

Set rng = Nothing
End Sub

When this code is plugged into the TimerTest sub, here is the result:

By using arrays and limiting the number of hits on the worksheet (i.e. limiting the interaction between Excel and VBA), we are able to produce a 1,000,000% increase in speed! Seriously, the lesson I've learned here is:

a) Read all your worksheet data into an array up front, try to manipulate it in VBA exclusively (no Worksheetfunction calls or temporary worksheets), and dump it back to the worksheet in one shot. Your watch will thank you.
a) Loops aren't intrinsically bad. It's the loops that require repeated hits on the worksheet (especially 1:1 ratio 'For Each x in Selection' type loops) that are costly. As I've mentioned before, when VBA and Excel have to interact, there is a time penalty.

Enjoy,
JP

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 3 Comment(s) on Trimming cells, before and after:

  1. Between the timer statements and your code, insert these statements:

    Application.ScreenUpdating = False ' beginning

    Application.ScreenUpdating = True ' end

    I'll bet this comes in closer to the array approach than the original worksheet approach.

  2. Well met, Jon!

    I added the ScreenUpdating option and here was the result:

    Old way: .125 seconds
    New way: .01 seconds

    I also added 'Calculation = xlCalculationManual' and got even more improvement from the original method:

    Old way: .11 seconds
    New way: .01 seconds

    There was an additional slight improvement when calculation was turned off as well. The array method wasn't really helped by turning calculation off, though; my guess is because the worksheet isn't being touched that much in the first place, so not much recalculation was taking place anyway.

    Thx,
    JP

  3. The array technique should be touching the worksheet twice, once to read and once to write, and reading is much faster than writing. The looping technique is more interactive, with one read and write per cell. Turning off Screen Updating and Caluclation will have a large effect on the loop, since these make each of many I/O operation faster. In the array, these steps make each of one operation faster.

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel