software

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

RgdsRichard Buttrey wrote:
gt; In a VBA macro I have a loop which processes 180 times and which
gt; either deletes a row or not. This takes a couple of seconds to
gt; complete.
gt;
gt; However when I open a second large 35Mb workbook file in memory, the
gt; macro takes about two and a half minutes. The only connection between
gt; the two workbooks is that the workbook with the looping macro,
gt; contains data which has previously been copied and pasted from the
gt; second workbook. That's the only time the workbooks have been
gt; 'connected'.
gt;
gt; There are no links between the two or any names in common. It doesn't
gt; appear to be a PC memory problem since I have oodles of RAM and in any
gt; case can open a third and larger workbook without it affecting the
gt; macro speed. As soon as I close the second workbook, everything is
gt; back to normal
gt;
gt; I'm at a loss to understand what's going on. As far as I can tell this
gt; has never been a problem in past months and has just arisen.
gt;
gt; The other extremely puzzling aspect is that if I put a break point
gt; before and after the loop, using the F5 key to run to the break point
gt; at the end of the loop takes 150 seconds, but holding the F8 key down
gt; stepping continuously through the loop takes only 40 seconds. Which
gt; doesn't seem to make sense. Why should a manual process take longer
gt; than letting the loop run automatically?
gt;
gt; Can anyone suggest what might be going on?
gt;
gt; Usual TIA
gt;
gt;
gt; The looping macro is below in case it's of any relevance. Delrow is a
gt; reference to a start cell A11, y starts out at an initial 180 and z at
gt; 0.
gt;
gt; For x = 1 To y
gt; If Delrow.Offset(x-z, 0) = quot;hidequot; Then
gt; Delrow.Offset(x-z, 0).EntireRow.Delete
gt; z = z 1
gt; End If
gt; Next
gt;
gt;
gt; __
gt; Richard Buttrey
gt; Grappenhall, Cheshire, UK
gt; __________________________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 Eldridgequot;Richardquot; wrote:

gt; Hi,
gt;
gt; Further observations if it helps.
gt;
gt; I've discovered that if I delete all the names in the second workbook
gt; (about 600 or so), the macro in the first workbook works as normal.
gt; Hence it seems the problem may be related in some way to range names.
gt; There are no names in the first workbook that reference the second
gt; workbook, i.e. no links, but there are about a dozen names which are
gt; the same names in both but which only refer to ranges in their own
gt; workbook.
gt;
gt; If this offers any clues as to the basic problem, I'd be exceedingly
gt; grateful for any input.
gt;
gt; Usual TIA
gt;
gt; Rgds
gt;
gt;
gt; Richard Buttrey wrote:
gt; gt; In a VBA macro I have a loop which processes 180 times and which
gt; gt; either deletes a row or not. This takes a couple of seconds to
gt; gt; complete.
gt; gt;
gt; gt; However when I open a second large 35Mb workbook file in memory, the
gt; gt; macro takes about two and a half minutes. The only connection between
gt; gt; the two workbooks is that the workbook with the looping macro,
gt; gt; contains data which has previously been copied and pasted from the
gt; gt; second workbook. That's the only time the workbooks have been
gt; gt; 'connected'.
gt; gt;
gt; gt; There are no links between the two or any names in common. It doesn't
gt; gt; appear to be a PC memory problem since I have oodles of RAM and in any
gt; gt; case can open a third and larger workbook without it affecting the
gt; gt; macro speed. As soon as I close the second workbook, everything is
gt; gt; back to normal
gt; gt;
gt; gt; I'm at a loss to understand what's going on. As far as I can tell this
gt; gt; has never been a problem in past months and has just arisen.
gt; gt;
gt; gt; The other extremely puzzling aspect is that if I put a break point
gt; gt; before and after the loop, using the F5 key to run to the break point
gt; gt; at the end of the loop takes 150 seconds, but holding the F8 key down
gt; gt; stepping continuously through the loop takes only 40 seconds. Which
gt; gt; doesn't seem to make sense. Why should a manual process take longer
gt; gt; than letting the loop run automatically?
gt; gt;
gt; gt; Can anyone suggest what might be going on?
gt; gt;
gt; gt; Usual TIA
gt; gt;
gt; gt;
gt; gt; The looping macro is below in case it's of any relevance. Delrow is a
gt; gt; reference to a start cell A11, y starts out at an initial 180 and z at
gt; gt; 0.
gt; gt;
gt; gt; For x = 1 To y
gt; gt; If Delrow.Offset(x-z, 0) = quot;hidequot; Then
gt; gt; Delrow.Offset(x-z, 0).EntireRow.Delete
gt; gt; z = z 1
gt; gt; End If
gt; gt; Next
gt; gt;
gt; gt;
gt; gt; __
gt; gt; Richard Buttrey
gt; gt; Grappenhall, Cheshire, UK
gt; gt; __________________________
gt;
gt;

Thanks Vic,

I'll certainly give it a try.

RgdsOn Wed, 29 Mar 2006 18:46:01 -0800, Vic Eldridge
gt; wrote:

gt;Hi Richard,
gt;
gt;I'm not exactly sure why it's behaving like it is, but will make this
gt;suggestion that may (or may not) avoid the problem.
gt;Instead of deleting the rows one by one, use the Union method to add the
gt;rows to a single, multiple area range, then delete that one range once your
gt;loop has finished.
gt;
gt;
gt;Regards,
gt;Vic Eldridge
gt;
gt;
gt;quot;Richardquot; wrote:
gt;
gt;gt; Hi,
gt;gt;
gt;gt; Further observations if it helps.
gt;gt;
gt;gt; I've discovered that if I delete all the names in the second workbook
gt;gt; (about 600 or so), the macro in the first workbook works as normal.
gt;gt; Hence it seems the problem may be related in some way to range names.
gt;gt; There are no names in the first workbook that reference the second
gt;gt; workbook, i.e. no links, but there are about a dozen names which are
gt;gt; the same names in both but which only refer to ranges in their own
gt;gt; workbook.
gt;gt;
gt;gt; If this offers any clues as to the basic problem, I'd be exceedingly
gt;gt; grateful for any input.
gt;gt;
gt;gt; Usual TIA
gt;gt;
gt;gt; Rgds
gt;gt;
gt;gt;
gt;gt; Richard Buttrey wrote:
gt;gt; gt; In a VBA macro I have a loop which processes 180 times and which
gt;gt; gt; either deletes a row or not. This takes a couple of seconds to
gt;gt; gt; complete.
gt;gt; gt;
gt;gt; gt; However when I open a second large 35Mb workbook file in memory, the
gt;gt; gt; macro takes about two and a half minutes. The only connection between
gt;gt; gt; the two workbooks is that the workbook with the looping macro,
gt;gt; gt; contains data which has previously been copied and pasted from the
gt;gt; gt; second workbook. That's the only time the workbooks have been
gt;gt; gt; 'connected'.
gt;gt; gt;
gt;gt; gt; There are no links between the two or any names in common. It doesn't
gt;gt; gt; appear to be a PC memory problem since I have oodles of RAM and in any
gt;gt; gt; case can open a third and larger workbook without it affecting the
gt;gt; gt; macro speed. As soon as I close the second workbook, everything is
gt;gt; gt; back to normal
gt;gt; gt;
gt;gt; gt; I'm at a loss to understand what's going on. As far as I can tell this
gt;gt; gt; has never been a problem in past months and has just arisen.
gt;gt; gt;
gt;gt; gt; The other extremely puzzling aspect is that if I put a break point
gt;gt; gt; before and after the loop, using the F5 key to run to the break point
gt;gt; gt; at the end of the loop takes 150 seconds, but holding the F8 key down
gt;gt; gt; stepping continuously through the loop takes only 40 seconds. Which
gt;gt; gt; doesn't seem to make sense. Why should a manual process take longer
gt;gt; gt; than letting the loop run automatically?
gt;gt; gt;
gt;gt; gt; Can anyone suggest what might be going on?
gt;gt; gt;
gt;gt; gt; Usual TIA
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; The looping macro is below in case it's of any relevance. Delrow is a
gt;gt; gt; reference to a start cell A11, y starts out at an initial 180 and z at
gt;gt; gt; 0.
gt;gt; gt;
gt;gt; gt; For x = 1 To y
gt;gt; gt; If Delrow.Offset(x-z, 0) = quot;hidequot; Then
gt;gt; gt; Delrow.Offset(x-z, 0).EntireRow.Delete
gt;gt; gt; z = z 1
gt;gt; gt; End If
gt;gt; gt; Next
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; __
gt;gt; gt; Richard Buttrey
gt;gt; gt; Grappenhall, Cheshire, UK
gt;gt; gt; __________________________
gt;gt;
gt;gt;

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________