Loop time seems dependent on unrelated workbook - Why?

  • Thread starter Thread starter Richard Buttrey
  • Start date Start date
R

Richard Buttrey

In a VBA macro I have a loop which processes 180 times and which
either deletes a row or not. This takes a couple of seconds to
complete.

However when I open a second large 35Mb workbook file in memory, the
macro takes about two and a half minutes. The only connection between
the two workbooks is that the workbook with the looping macro,
contains data which has previously been copied and pasted from the
second workbook. That's the only time the workbooks have been
'connected'.

There are no links between the two or any names in common. It doesn't
appear to be a PC memory problem since I have oodles of RAM and in any
case can open a third and larger workbook without it affecting the
macro speed. As soon as I close the second workbook, everything is
back to normal

I'm at a loss to understand what's going on. As far as I can tell this
has never been a problem in past months and has just arisen.

The other extremely puzzling aspect is that if I put a break point
before and after the loop, using the F5 key to run to the break point
at the end of the loop takes 150 seconds, but holding the F8 key down
stepping continuously through the loop takes only 40 seconds. Which
doesn't seem to make sense. Why should a manual process take longer
than letting the loop run automatically?

Can anyone suggest what might be going on?

Usual TIA


The looping macro is below in case it's of any relevance. Delrow is a
reference to a start cell A11, y starts out at an initial 180 and z at
0.

For x = 1 To y
If Delrow.Offset(x-z, 0) = "hide" Then
Delrow.Offset(x-z, 0).EntireRow.Delete
z = z+1
End If
Next


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Hi,

Further observations if it helps.

I've discovered that if I delete all the names in the second workbook
(about 600 or so), the macro in the first workbook works as normal.
Hence it seems the problem may be related in some way to range names.
There are no names in the first workbook that reference the second
workbook, i.e. no links, but there are about a dozen names which are
the same names in both but which only refer to ranges in their own
workbook.

If this offers any clues as to the basic problem, I'd be exceedingly
grateful for any input.

Usual TIA

Rgds
 
Hi Richard,

I'm not exactly sure why it's behaving like it is, but will make this
suggestion that may (or may not) avoid the problem.
Instead of deleting the rows one by one, use the Union method to add the
rows to a single, multiple area range, then delete that one range once your
loop has finished.


Regards,
Vic Eldridge
 
Thanks Vic,

I'll certainly give it a try.

Rgds


Hi Richard,

I'm not exactly sure why it's behaving like it is, but will make this
suggestion that may (or may not) avoid the problem.
Instead of deleting the rows one by one, use the Union method to add the
rows to a single, multiple area range, then delete that one range once your
loop has finished.


Regards,
Vic Eldridge
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Back
Top