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?

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

On Jan 24, 10:07 pm, guillermo.ht
wrote:
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?

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

"guillermo.ht" wrote in message
...
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?

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"okrob" wrote:

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

On Jan 24, 10:07 pm, guillermo.ht
wrote:
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?