this macro is slowing down my spreadsheet

[guillermo.ht]

I have an auto_open macro that executes several other macros, but it appears
to take a long time to finish these steps. When is ESC the process to see
where it stopped executing... is on the last "Next" statement in the
following macro:

Sub trimcells()
Sheets("manifest").Rows("5:250").Select
Dim cell As Range
On Error Resume Next 'In case no cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Trim(cell.Value)
Next
End Sub

Is this macro a loop or can it be shortened? Did i do something wrong?

 

[okrob]

Well, yes... It's a loop. you could try to turn off screen
updating...
Application.ScreenUpdating = False
just make sure to turn it back on again after the macro is finished.
Application.ScreenUpdating = True

 

[NickHK]

Not sure if it is faster, but is this what you want:

Dim cell As Range
For Each cell In
Worksheets("manifest").Rows("5:250").SpecialCells(xlConstants, xlTextValues)
cell.Value = Trim(cell.Value)
Next

NickHK

 

[Martin Fishlock]

Hi

It may also be faster to try this instead of selecting them specificly and
adding okrob's suggestion:


Sub trimcellsnew()
Dim cell As Range

Application.ScreenUpdating=False

On Error Resume Next 'In case no cells in selection

For Each cell In _
Sheets("sheet1").Rows("5:250").SpecialCells( _
xlConstants, xlTextValues)
cell.Value = Trim(cell.Value)
Next
Application.ScreenUpdating=true
End Sub