0

I have written code in VBA that removes some potential spaces between characters. The code works pretty well but becomes really slow when the file contains thousands of rows. I'd like to know if it's possible to improve it, in order to reduce the time of operation, but also mainly to stop the file from freezing. Here is the code:

Sub Test()
  Dim cell as Range
  Dim sht As Worksheet
  Dim LastRow As Long
  Dim StartCell As Range
  Dim areaToTrim As Range
  Set sht = ThisWorkbook.Worksheets("SS upload")
  Set StartCell = sht.Range("A14")
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  Set areaToTrim = sht.Range("B14:B" & LastRow)
  For Each cell In areaToTrim
    cell.Value = Trim(cell.Value)
  Next cell
End Sub
Community
  • 1
  • 1
Jeofbist3
  • 47
  • 1
  • 6

3 Answers3

1

The fastest way is to read the range into an array, trim it there and then write it back to the range:

Sub Test()

  Dim sht As Worksheet
  Dim LastRow As Long
  Dim StartCell As Range
  Dim areaToTrim As Range
  Dim varArray() As Variant
  Dim i As Long

  Set sht = ThisWorkbook.Worksheets("SS upload")
  Set StartCell = sht.Range("A14")
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  Set areaToTrim = sht.Range("B14:B" & LastRow)
  varArray = areaToTrim ' Read range into array
  For i = LBound(varArray, 1) To UBound(varArray, 1)
    varArray(i, 1) = Trim(varArray(i, 1))
  Next i
  areaToTrim.Value = varArray ' Write array back to range

End Sub

No need to worry about Application.ScreenUpdating or Application.Calculation. Nice and simple!

If you are still worried about any responsiveness, put a DoEventsin the body of the loop.

robinCTS
  • 5,746
  • 14
  • 30
  • 37
  • Thanks robinCTS, your solution is magic and works pretty well! No freeze anymore and so much faster. Have a nice day :) – Jeofbist3 Aug 28 '17 at 12:17
  • @Jeofbist3 Don't forget to accept the best, correct, complete answer (if there is one) and up vote any answer that is also correct/useful. From your history it looks like you haven't been doing so. I suggest you revisit all you previous posts and do this :) – robinCTS Aug 28 '17 at 12:55
0

Try like this, to reduce screenupdating. This is a piece of code, that I always use, thus some of the commands are probably a bit too much for the current question, but they can be still useful.

As a second point - do not declare a variable with the name Cell, you can suffer a bit from this later. Declare it rngCell or myCell or anything else, which is not part of the VBE variables.

Public Sub TestMe()

    Call OnStart
    'YourCode
    Call OnEnd

End Sub    

Public Sub OnEnd()

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic
    ThisWorkbook.Date1904 = False

    Application.StatusBar = False

End Sub

Public Sub OnStart()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    Application.Calculation = xlAutomatic
    ThisWorkbook.Date1904 = False

    ActiveWindow.View = xlNormalView

End Sub

If you feel like it, you may save the range as an array and do the trim operation there. However, it may overcomplicate your code, if you are not used to work with arrays - Trim Cells using VBA in Excel

Vityata
  • 42,633
  • 8
  • 55
  • 100
0

You can prevent the freezing when you insert DoEvents in your loop. And then execute it, say every hundredth time. This will make the loop run a little slower, but allows the user to use the GUI meanwhile.

...
Dim cnt As Integer
For Each cell In areaToTrim
    cell.Value = Trim(cell.Value)

    cnt=cnt + 1
    If cnt Mod 100 = 0 Then
        DoEvents
    End If
Next cell
...

You can play around with the number to optimize it for your needs.

DoEvents brings also some problems with it. A good explanation about DoEvents can be found here.

MatSnow
  • 7,357
  • 3
  • 19
  • 31