Speed up my code????

[powercell99]

I need help in speeding up my code. it takes forever to run......

Any suggestions or edits would be greatly appreciated.

Sub TrimSpaces()
  Dim Cell As Range
  For Each Cell In ActiveSheet.UsedRange.Columns("L").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
    For Each Cell In ActiveSheet.UsedRange.Columns("M").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("N").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("O").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("Q").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("R").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("S").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("T").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("U").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("M").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("V").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("Y").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("Z").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("AA").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("AB").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("AD").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("AE").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
      For Each Cell In ActiveSheet.UsedRange.Columns("AF").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
        For Each Cell In ActiveSheet.UsedRange.Columns("AG").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
        For Each Cell In ActiveSheet.UsedRange.Columns("AH").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
        For Each Cell In ActiveSheet.UsedRange.Columns("AI").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
        For Each Cell In ActiveSheet.UsedRange.Columns("D").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
        For Each Cell In ActiveSheet.UsedRange.Columns("C").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
        For Each Cell In ActiveSheet.UsedRange.Columns("F").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
        For Each Cell In ActiveSheet.UsedRange.Columns("G").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
        For Each Cell In ActiveSheet.UsedRange.Columns("I").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
        For Each Cell In ActiveSheet.UsedRange.Columns("J").Cells
    x = x + 1
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
  
    
  
End Sub

 

[John_w]

Try something like this. Just add other columns to the Range statements.

Sub TrimSpaces2()
    
    Dim r As Long, c As Long
    Dim vals
    
    With ActiveSheet.UsedRange
        vals = Range(.Columns("L").Cells, .Columns("M").Cells)
    End With
    
    For r = 1 To UBound(vals)
        For c = 1 To UBound(vals, 2)
            vals(r, c) = Trim(vals(r, c))
        Next
    Next
    
    With ActiveSheet.UsedRange
        Range(.Columns("L").Cells, .Columns("M").Cells) = vals
    End With
   
End Sub

 

[AlphaFrog]

Another method...

[color=darkblue]Sub[/color] TrimSpaces()
  [color=darkblue]Dim[/color] rngArea [color=darkblue]As[/color] Range, v [color=darkblue]As[/color] [color=darkblue]Variant[/color], r [color=darkblue]As[/color] [color=darkblue]Long[/color], c [color=darkblue]As[/color] Long
  
  Application.ScreenUpdating = [color=darkblue]False[/color]
  Application.EnableEvents = [color=darkblue]False[/color]
  Application.Calculation = xlCalculationManual
  
  [color=darkblue]For[/color] [color=darkblue]Each[/color] rngArea [color=darkblue]In[/color] Intersect(ActiveSheet.UsedRange, Range("C:D,F:G,I:J,L:O,Q:V,Y:AB,AD:AI")).Areas
    v = rngArea.Value
    [color=darkblue]For[/color] r = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](v, 1)
        [color=darkblue]For[/color] c = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](v, 2)
            v(r, c) = Application.WorksheetFunction.Trim(v(r, c))
    [color=darkblue]Next[/color] c, r
    rngArea.Value = v
  [color=darkblue]Next[/color] rngArea
  
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = [color=darkblue]True[/color]
  Application.ScreenUpdating = [color=darkblue]True[/color]
  
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[powercell99]

Another method...

[COLOR=darkblue]Sub[/COLOR] TrimSpaces()
  [COLOR=darkblue]Dim[/COLOR] rngArea [COLOR=darkblue]As[/COLOR] Range, v [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], c [COLOR=darkblue]As[/COLOR] Long
  
  Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
  Application.EnableEvents = [COLOR=darkblue]False[/COLOR]
  Application.Calculation = xlCalculationManual
  
  [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rngArea [COLOR=darkblue]In[/COLOR] Intersect(ActiveSheet.UsedRange, Range("C:D,F:G,I:J,L:O,Q:V,Y:AB,AD:AI")).Areas
    v = rngArea.Value
    [COLOR=darkblue]For[/COLOR] r = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](v, 1)
        [COLOR=darkblue]For[/COLOR] c = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](v, 2)
            v(r, c) = Application.WorksheetFunction.Trim(v(r, c))
    [COLOR=darkblue]Next[/COLOR] c, r
    rngArea.Value = v
  [COLOR=darkblue]Next[/COLOR] rngArea
  
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = [COLOR=darkblue]True[/COLOR]
  Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
  
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Wow, Thats an incredible difference. My original code was taking about 4 minutes to run, but these are so FAST. Works perfectly. Thanks for much for you help! I figured the reason mine was taking so long was that it was looping down through each row before moving to the next column and then looping down through all the rows again. So it was looping 26 times which took 26 times as long. But these new methods work across first then down in the loop only once. Is there another reason that makes it faster?

Again, I really appreciate your time and solution. Thanks.

Larry

 

[powercell99]

Thanks for the suggestion, this works much better than my original attempt.

 

[shadow12345]

using things like.

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

do help, especially if its a lot of data... but you pretty much nailed it in your earlier post i think.

 

[powercell99]

using things like.

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

do help, especially if its a lot of data... but you pretty much nailed it in your earlier post i think.

Awesome. Thanks alot!

Larry

 

[AlphaFrog]

I figured the reason mine was taking so long was that it was looping down through each row before moving to the next column and then looping down through all the rows again. So it was looping 26 times which took 26 times as long. But these new methods work across first then down in the loop only once. Is there another reason that makes it faster?

Again, I really appreciate your time and solution. Thanks.

Larry

You're welcome.

Another speed improvement was the number of times the worksheet is accessed. It's expensive to access the worksheet and your original code did it for every used cell in the specified columns. If you only had a few hundred cells, it probably wouldn't matter much. I assume you had several thousand rows of data so it took four minutes.

The new code reads all the values of a column (or contiguous group of columns) into a variant array en masse. This is one worksheet access. Then it processes the array and writes the values back to the worksheet en masse. This is a second worksheet access for all the cells in a column group. So two worksheet calls for a column group versus thousands equals a notable speed improvement.

 

[powercell99]

Thats very complicated. Thanks for your insight on how it works. very interesting.

You're welcome.

Another speed improvement was the number of times the worksheet is accessed. It's expensive to access the worksheet and your original code did it for every used cell in the specified columns. If you only had a few hundred cells, it probably wouldn't matter much. I assume you had several thousand rows of data so it took four minutes.

The new code reads all the values of a column (or contiguous group of columns) into a variant array en masse. This is one worksheet access. Then it processes the array and writes the values back to the worksheet en masse. This is a second worksheet access for all the cells in a column group. So two worksheet calls for a column group versus thousands equals a notable speed improvement.