Trim multiple cells using a Do-Until loop is a slow method.

[worldofinterest]

I receive a weekly csv file from a database where several pieces of data have 3 or 4 empty spaces in a cell, depending on the database character type length.

I have set a routine to loop through 3 columns of data to trim each cell in turn (sample attached).
This routine, even with screen updating disabled is very slow.

Can you please suggest a faster method to trim the cells.

Many thanks
David

Option Explicit

Sub Trim_Cells()


'This routine trims cells in Columns 1, 3 and 5 using a Do-Until Loop


'Declare Variables
    Dim tStart As Date
    Dim tFinish As Date
    Dim tRunTime As Double
    Dim R As Integer    'Row Number


'Switch off screen refresh
    Application.ScreenUpdating = 0


'Record Start Time
    tStart = Now()
    
'Set user message in the staus bar
    Application.StatusBar = "Trimming Vendor Codes.  Please be patient"


    R = 7 'First data row
'Loop through all job records
    Do Until Cells(R, 5) = ""           'Test for empty cell
        Cells(R, 1) = Trim(Cells(R, 1)) 'Trim Client_Code
        Cells(R, 3) = Trim(Cells(R, 3)) 'Trim Vendor_Code
        Cells(R, 5) = Trim(Cells(R, 5)) 'Trim Booking Ref
        R = R + 1
    Loop


'Record the finish time
    tFinish = Now()
    
'Reset status bar message
    Application.StatusBar = False


'Calculate and show time taken for routine to run.
    tRunTime = (tFinish - tStart) * 24 * 60 * 60
    tRunTime = MsgBox("Time taken for routine is " & Round(tRunTime, 5) & " seconds", , "Run Time")


End Sub

 

[Jonmo1]

Try

Sub test()
Dim MyArray
With Range("A1:B4")
    MyArray = Application.Trim(.Value)
    .Value = MyArray
End With
End Sub

You'll have to adjust the range accordingly.

 

[Jonmo1]

For your specific range setup (row 7 to ??, columns 1 3 and 5)

Sub test()
Dim MyArray, lr As Long, i As Long
lr = Cells(Rows.Count, 5).End(xlUp).Row
For i = 1 To 5 Step 2
With Range(Cells(7, i), Cells(lr, i))
    MyArray = Application.Trim(.Value)
    .Value = MyArray
End With
Next i
End Sub

 

[mrmmickle1]

What if you just use a formula like this:

[COLOR=#008000]      'Insert 3 Columns[/COLOR]
      Columns("A:C").Insert Shift:=xlToRight
     [COLOR=#008000] 'Get Values[/COLOR]
      Range("A2:A" & lr).Formula = "=Trim(A2)"
      Range("B2:B" & lr).Formula = "=Trim(C2)"
      Range("C2:C" & lr).Formula = "=Trim(E2)"
   [COLOR=#008000]   'Copy and Paste As Values[/COLOR]
      Columns("A:C").Copy      
      Range("A1").PasteSpecial Paste:=xlPasteValues
    [COLOR=#008000]  'Delete Old Columns[/COLOR]
      Columns(4).Delete: Columns(5).Delete: Columns(6).Delete
     [COLOR=#008000] 'Insert Columns to arrange fields properly[/COLOR]
      Columns(4).Cut: Columns(2).Insert Shift:=xlToRight      
      Columns(5).Cut: Columns(4).Insert Shift:=xlToRight

I'd put money on Jonmo1's method being the fastest. Is it possible to run all three of these code methods with a timer and give us the resulting times? (Just for future reference)

Original Code-
JonMo1-
Formula Code-

 

[Rick Rothstein]

For your specific range setup (row 7 to ??, columns 1 3 and 5)

Sub test()
Dim MyArray, lr As Long, i As Long
lr = Cells(Rows.Count, 5).End(xlUp).Row
For i = 1 To 5 Step 2
With Range(Cells(7, i), Cells(lr, i))
    MyArray = Application.Trim(.Value)
    .Value = MyArray
End With
Next i
End Sub

It appears that you can bypass MyArray completely...

Sub test()
  Dim lr As Long, i As Long
  lr = Cells(Rows.Count, 5).End(xlUp).Row
  For i = 1 To 5 Step 2
    With Range(Cells(7, i), Cells(lr, i))
      .Value = Application.Trim(.Value)
    End With
  Next i
End Sub

 

[worldofinterest]

Thank you so much everybody:

RickRothstein, Jonmo1 - both worked very fast indeed!

In terms of simplicity and elegance of coding, my personal preference goes to RickRothstein.

My knowledge iof Arrays is pretty sketchy, so I'm very happy that it has been highlighted that this a place where an array can be used.

mrmickle1, thank you, but inserting columns, entering a formula and deleting columns is something I am trying not to use, if possible. It's very code-heavy and prone to mistakes.

 

[Jonmo1]

Glad to help, thanks for the feedback.