Macro Running Slow

[Guest]

I have used the "Record a New Macro" tool often with great luck. Today (with brand new office 2003 version) I recorded a macro to insert a row below the selected row, then merge merge and format left (2 steps) cells atop each other (A10 with A11 then B10 with B11 and so on). The merging and formatting only takes place in 13 Columns (A through M). The process however takes about 5 or 6 full seconds, which takes a lot of time when I need to run it 500 times or so. I have done a lot of much more complex macros and they always execute almost instantly, but I can sit and watch this one execute barely faster than I can do it myself. Does anyone know what could cause a macro to run so slowly

Brand


I will include the text of the macro if anyone wants to look at it

Sub Merge_Rows(

' Merge_Rows Macr
' Macro recorded 1/28/2004 by Brandt Saxe

' Keyboard Shortcut: Ctrl+

ActiveCell.Offset(1, 0).Range("A1").Selec
Selection.EntireRow.Inser
ActiveCell.Offset(-1, -2).Range("A1:A2").Selec
With Selectio
.HorizontalAlignment = xlGenera
.VerticalAlignment = xlCente
.WrapText = Fals
.Orientation =
.AddIndent = Fals
.IndentLevel =
.ShrinkToFit = Fals
.ReadingOrder = xlContex
.MergeCells = Tru
End Wit
ActiveCell.Offset(0, 1).Range("A1:A2").Selec
With Selectio
.HorizontalAlignment = xlGenera
.VerticalAlignment = xlCente
.WrapText = Fals
.Orientation =
.AddIndent = Fals
.IndentLevel =
.ShrinkToFit = Fals
.ReadingOrder = xlContex
.MergeCells = Tru
End Wit
ActiveCell.Offset(0, 1).Range("A1:A2").Selec
With Selectio
.HorizontalAlignment = xlGenera
.VerticalAlignment = xlCente
.WrapText = Tru
.Orientation =
.AddIndent = Fals
.IndentLevel =
.ShrinkToFit = Fals
.ReadingOrder = xlContex
.MergeCells = Tru
End Wit
ActiveCell.Offset(0, 1).Range("A1:A2").Selec
With Selectio
.HorizontalAlignment = xlGenera
.VerticalAlignment = xlCente
.WrapText = Tru
.Orientation =
.AddIndent = Fals
.IndentLevel =
.ShrinkToFit = Fals
.ReadingOrder = xlContex
.MergeCells = Tru
End Wit
ActiveCell.Offset(0, 1).Range("A1:A2").Selec
With Selectio
.HorizontalAlignment = xlCente
.VerticalAlignment = xlCente
.WrapText = Tru
.Orientation =
.AddIndent = Fals
.IndentLevel =
.ShrinkToFit = Fals
.ReadingOrder = xlContex
.MergeCells = Tru
End Wit
ActiveCell.Offset(0, 1).Range("A1:A2").Selec
With Selectio
.HorizontalAlignment = xlGenera
.VerticalAlignment = xlCente
.WrapText = Tru
.Orientation =
.AddIndent = Fals
.IndentLevel =
.ShrinkToFit = Fals
.ReadingOrder = xlContex
.MergeCells = Tru
End Wit
ActiveCell.Offset(0, 1).Range("A1:A2").Selec
With Selectio
.HorizontalAlignment = xlGenera
.VerticalAlignment = xlCente
.WrapText = Tru
.Orientation =
.AddIndent = Fals
.IndentLevel =
.ShrinkToFit = Fals
.ReadingOrder = xlContex
.MergeCells = Tru
End Wit
ActiveCell.Offset(0, 1).Range("A1:A2").Selec
With Selectio
.HorizontalAlignment = xlGenera
.VerticalAlignment = xlCente
.WrapText = Tru
.Orientation =
.AddIndent = Fals
.IndentLevel =
.ShrinkToFit = Fals
.ReadingOrder = xlContex
.MergeCells = Tru
End Wit
ActiveCell.Offset(0, 1).Range("A1:A2").Selec
With Selectio
.HorizontalAlignment = xlGenera
.VerticalAlignment = xlCente
.WrapText = Tru
.Orientation =
.AddIndent = Fals
.IndentLevel =
.ShrinkToFit = Fals
.ReadingOrder = xlContex
.MergeCells = True
End With
ActiveCell.Offset(0, 1).Range("A1:A2").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
ActiveCell.Offset(0, 1).Range("A1:A2").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
ActiveCell.Offset(0, 1).Range("A1:A2").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
ActiveCell.Offset(0, 1).Range("A1:A2").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.RowHeight = 11.25
ActiveCell.Offset(1, -10).Range("A1").Select
End Sub

 

[Frank Kabel]

Hi Brandt
the problem with the macro redorder is that it record exactly what you
did. All selections, etc. You have to clean up your code after
recording. In your case I did the following (the code executes now
nearly without any delay)
1. Removal of all .Select statement as they are in most cases not
required
2. Combine your 13 format parts into one (with a For - Next clause)
3. Disable screenupdating while executing the code

Some notes to the formating:
-I left all your format properties though I think you don't need them
all
- Your code formated the cell vertically centered. I left that

HTH
Frank

---------

Sub Merge_Rows()
Dim rng As Range
Dim col_count As Integer
Application.ScreenUpdating = False
Set rng = Cells(ActiveCell.Row, 1)
rng.Offset(1, 0).EntireRow.Insert
For col_count = 1 To 13
With rng.Offset(0, col_count - 1).Resize(2, 1)
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Next col_count
rng.RowHeight = 11.25
rng.Select
Application.ScreenUpdating = True
End Sub

 

[BrianB]

1. You can tell part of the answer straight away - look at how many
lines there are ! (You can get rid of all the ones you don't really
need).

2. Formatting cells takes ages with a macro. (It is far quicker, if you
can, to have a preformatted sheet and copy/paste special/formats.

3. Put at the start :
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
at the end
Application.Calculation = xlCalculationAutomatic at the end.)
Application.ScreenUpdating = True