Macro running extremely slowly

Question

I've been writing a macro which inserts a new row in a holiday sheet for a new recruit, now it was working fine, but now it has ground to a halt and takes a huge amount of time to insert a new row. Here is the affected code:

For f = 1 To Worksheets.Count - 1

    Worksheets(f).Select

    Range("A1").Select

    If Worksheets(f).Name = "FLEXI" Then

        Range("A1").Select
        N = Range("A2").Value
        Range("A" & NewRow).Select
        Selection.EntireRow.Insert
        Range("A" & NewRow + N + 1).Select
        Selection.EntireRow.Insert
        GoTo flexidivert
    End If

Range("A" & NewRow).Select
Selection.EntireRow.Insert

flexidivert:
Next f

I have screenupdating disabled, enableevents disabled and calculations set to manual so the usual suspects aren't at work here. It's such a simple procedure as well, I can't understand why it takes minutes to compute. NewRow is the row to be pasted in and worksheets.count = 5 so there aren't a huge number to scroll through.

Using .Select really slows down the code... See this link http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select/10718179#10718179 — Siddharth Rout, Apr 10 '13 at 08:52
why do you need to loop through the worksheets, if you know the name why not just reference the worksheet directly and select it. — Our Man in Bananas, Apr 10 '13 at 09:03
Also, I think everytime you insert a row, Excels calculation engine checks all the cells, and all the range names are redefined...so if you have any formulae, disable automatic calculation first — Our Man in Bananas, Apr 10 '13 at 09:04
@Philip: I guess that is because the insert row is happening for all sheet. In case of `Flexi`, there is an additional `Range("A" & NewRow + N + 1)` Also the has already mentioned that he/she is setting the calculation to manual :) — Siddharth Rout, Apr 10 '13 at 09:05
+1, ok but not for the last one, regardless of it's name? Shouldn't it be **For f = 1 To Worksheets.Count** ? — Our Man in Bananas, Apr 10 '13 at 09:10
`For f = 1 To Worksheets.Count` Yes it should be that if you are looping through `ALL` worksheets. Only OP can say why he/she is using `Worksheets.Count-1` — Siddharth Rout, Apr 10 '13 at 09:17

score 1 · Accepted Answer · answered Apr 10 '13 at 08:58

1

Further to my comments above, Is this what you are trying to do?

For f = 1 To Worksheets.Count - 1
    With Worksheets(f)
        .Range("A" & NewRow).EntireRow.Insert

        If .Name = "FLEXI" Then
            N = .Range("A2").Value
            .Range("A" & NewRow + N + 1).EntireRow.Insert
        End If
    End With
Next f

I am assuming that you have declared all your variables correctly and Cell A2 has numberic values.

answered Apr 10 '13 at 08:58

Siddharth Rout

147,039
17
206
250

I know using select can slow it down, but not to the degree that it is doing, it's taking minutes to complete it. Thank you for your solution, I have gone back to a previous version instead which has all the same code but doesn't have the speed issue. – user1545643 Apr 10 '13 at 09:03

score 0 · Answer 2 · answered Apr 10 '13 at 09:01

try the below

Worksheets("FLEXI").select
N = Range("A2").Value
Range("A" & NewRow).EntireRow.Insert shift:=xldown 
Range("A" & NewRow + N + 1).EntireRow.Insert shift:=xldown
Range("A" & NewRow).EntireRow.Insert shift:=xldown

no need to select a cell before using INSERT

also, if you know the name of the woreksheet no need to loop, just reference it directly through the collection

HTH

Philip

Macro running extremely slowly

2 Answers2