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
y=y-1
Next
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
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
y=y-1
Next
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________