macro is very slow . . .

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ron.Winkley@gmail.com - 15 Dec 2007 10:49 GMT

Hi to All,

I made the following very simple macro
saved it in Personal.xls
it works, but
it runs *very* slowly

Any suggestions to speed it up?

Thank you
============================

Sub InsertBlankRows()
'
' InsertBlankRows Macro
' Macro recorded 13 12 2007 by Consultant
'
'
For nn = 1 To 10

Selection.EntireRow.Insert
ActiveCell.Offset(2, 0).Select

Next nn

End Sub
===============================

Reply to this Message

Ron.Winkley@gmail.com - 15 Dec 2007 11:06 GMT

PS: "very slow" = about 6 seconds for each loop!
my machine has 2GB of RAM and runs many other,
much more complicated macros a lot faster . . .

On Dec 15, 10:49 am, Ron.Wink...@gmail.com wrote:

> Hi to All,
>
[quoted text clipped - 23 lines]
> End Sub
> ===============================

Reply to this Message

Dave Peterson - 15 Dec 2007 11:50 GMT

I'm not sure if this will help, but it shouldn't take long to test.

Saved from a previous post.

If you can see the pagebreak dotted lines, then excel will slow down.
If you're in View|Page break preview mode, then excel will slow down.

Turning off .screenupdating and changing the .calculationmode to manual may help
speed things up:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'your code here

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode
Application.ScreenUpdating = True

End Sub

> Hi to All,
>
[quoted text clipped - 23 lines]
> End Sub
> ===============================

Signature

Dave Peterson

Reply to this Message

Don Guillett - 15 Dec 2007 13:00 GMT

You may like this better without selections.

Sub inserteveryotherrow()
For i = 10 To 2 Step -1
Rows(i).Insert
Next i
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi to All,
>
[quoted text clipped - 23 lines]
> End Sub
> ===============================

Reply to this Message