How can I make this faster?

[pholt33]

I often receive data files that need some cleanup so I have created a macro to trim and remove carriage returns. It works well and is pretty quick (few seconds) when the range is, say, 1500 cells but its much slower (60+ secs) if there are 5-10k+. How can I speed this up? Or does someone have a different macro that accomplishes the same/similar thing?

Sub DeleteLeadingTrailingSpaces()

Application.ScreenUpdating = False

Set W = Application.Selection
Set W = Application.InputBox("Select one range that you want to remove leading and trailing spaces:", "RemoveLeadingAndTrailingSpaces", W.Address, Type:=8)
For Each R In W
R.Value = VBA.Trim(R.Value)
R.Value = Application.WorksheetFunction.Clean(R.Value)
Next

End Sub

 

[jasonb75]

Try setting calculation to manual if you have formulas. Also disable events if you have and (worksheet change, etc).

 

[pholt33]

No formulas or anything at the time when I am doing the cleanup. Any other options?

 

[Oaktree]

Looping through each cell in a range is VERY slow.

Instead, load the range to an array, loop through the array & load (either to the same array or a new array) the modified values, then write the modified array back to the range.

Chip Pearson's website has a write-up with the basics here: Arrays And Ranges In VBA

 

[Jon Peltier]

Just for fun...

Sub DeleteLeadingTrailingSpaces()

  Application.ScreenUpdating = False

  If TypeName(Selection) = "Range" Then
    Dim sSelection As String
    sSelection = Selection.Address
  End If
  On Error Resume Next
  Dim rSelection As Range
  Set rSelection = Application.InputBox("Select one range that you want to remove leading and trailing spaces:", "RemoveLeadingAndTrailingSpaces", sSelection, Type:=8)
  If rSelection Is Nothing Then GoTo ExitSub
  On Error GoTo 0
 
  If rSelection.Cells.Count > 1 Then
    Dim vSelection As Variant
    vSelection = rSelection.Value
  Else
    ReDim vSelection(1 To 1, 1 To 1)
    vSelection(1, 1) = rSelection.Value
  End If
 
  Dim iRow As Long, iCol As Long
  For iRow = LBound(vSelection, 1) To UBound(vSelection, 1)
    For iCol = LBound(vSelection, 2) To UBound(vSelection, 2)
      vSelection(iRow, iCol) = WorksheetFunction.Clean(Trim(vSelection(iRow, iCol)))
    Next
  Next
 
  rSelection.Value = vSelection

ExitSub:
  Application.ScreenUpdating = True

End Sub

 

[pholt33]

On a file I needed to cleanup, my code took 57 seconds and yours was done in a split second. ?
Jon, what makes your solution so much faster?

 

[Jon Peltier]

It takes time for VBA to access the worksheet, reading values and especially writing values.

Your code worked cell by cell, reading each value from the worksheet, changing the value, then writing the value back into the worksheet. Many read and write operations, so it's very slow.

My code reads the entire range of values in one step, changes the values one by one, then writes the entire range of values back into the worksheet in one step. One read and one write operation, so it's very fast.