Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
Range Name affecting speed of macro
I posted a question back in March, (thread "Loop time seems dependent
on unrelated workbook - Why?"), and have finally managed to identify the problem area but still don't know what is causing it. Hence this is more a general request for suggestions as to the cause, since I have now put in a work around. The problem was this. I have three workbooks. In Book1 (which contains about 600 range names), a database sheet, (which contains no formulae or names) is fed from another working 'paper' sheet. This database sheet is first output as an independent file to create a second workbook. Book 2 is used as a data import feed to a database sheet in a third workbook. (Book2 is also used by other applications which is why I don't feed directly from book1 to book3) Book 3 contains amongst others, a database sheet, (fed by a copy and paste from Book2), which is used by various other report template sheets. The 3 Workbooks. contain no links or range names in common. The third reporting workbook has various macros which filter and summarise data, hide rows, export data files and various other stuff, based on a user choosing from various criteria. This reporting workbook had been working fine for 18 months or so, but last month it started a go slow campaign. A macro which typically took about 30-45 seconds to process started taking many minutes. I finally highlighted the problem data. The first reporting Wb contains about 600 range names. The working paper sheet and the database sheet (which is filtered from the working paper sheet), each have circa 2000 rows of data. By iteratively deleting half the range names in Book1, exporting the data to the 2nd Wb. importing to the 3rd Wb, running the macro in the third Wb, noting the time the macro took, and then either deleting half the remaining names in Book1, or re-opening Book1 and deleting the other half of the names, I eventually found one particular range name which was causing the slow running. When I deleted just this one name from Book1, everything was back to normal. (Deleting it and then re-creating it did not solve the problem). I am totally perplexed why this one name in Book1 should cause a problem in what is essentially an independent, (no links or common names) Book3. The only other thing I should add is that the problem only exists when Book1 is open in memory at the same time as Book3. I'd be grateful for any ideas or suggestions as to what may be going on here. Usual TIA. __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying a Selected Range in a Macro | Excel Discussion (Misc queries) | |||
Macro to Highlight Range | Excel Discussion (Misc queries) | |||
format macro | Excel Discussion (Misc queries) | |||
Macro - define cell range for a sum function | Excel Discussion (Misc queries) | |||
Macro for Show/Hide Range | Excel Discussion (Misc queries) |