| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop time seems dependent on unrelated workbook - Why?
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. |
|
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Loop time seems dependent on unrelated workbook - Why?
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 Richard Buttrey wrote: 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 __________________________ |
|
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Loop time seems dependent on unrelated workbook - Why?
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 "Richard" wrote: 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 Richard Buttrey wrote: 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 __________________________ |
|
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Loop time seems dependent on unrelated workbook - Why?
Thanks Vic,
I'll certainly give it a try. Rgds On Wed, 29 Mar 2006 18:46:01 -0800, Vic Eldridge wrote: 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 "Richard" wrote: 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 Richard Buttrey wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying data from one workbook to another dependent on user select | Excel Discussion (Misc queries) | |||
| Cell Value dependent upon the same cells value in another workbook | Excel Discussion (Misc queries) | |||
| enter a static time dependent upon another cell value | Excel Discussion (Misc queries) | |||
| Loop time seems dependent on unrelated workbook - Why? | Excel Worksheet Functions | |||
| Using Validation List from Another Workbook with Dependent Data | Excel Worksheet Functions | |||
Linear Mode
