Hi there,
I have a Excel 2003 workbook with 10-some sheets. One sheet contains several lists on which named ranges are based (about 20 named ranges). The named ranges are used on several other sheets to perform data validation. I have a few sheets with 4-5 colums which have data validated cells, based on those named ranges. Some ranges are static, other are dynamic .
When i start Excel and open this workbook, performance is adequate. As soon as i have switched between the data validated columns 10-20 times (by moving the cursor right and left), performance begins to decrease to a point where changing columns (e.g. go from column a to b) takes 2-5 seconds, where cpu is 100% during this 2-5 seconds. Only after the 2-5 seconds, the down arrow for the data validated cell appears.
It seems like Excel is performing every calculation involved in every data validated cell every time i move to another cell, and doesn't release memory when doing it. Now i know dat many data validated cells influence performance, but this is unworkable. And the number of data validated cells doesn;t seem ridicilous high to me.
Any ideas?
PS. it doens't matter how many rows in the sheet are filled, behaviour starts when 0 rows are filled. Data validation is defined on 5 columns of 1000 cells.
PS Asus Notebook with 1Gb Ram, Win XP, 1.4 GHZ
** Update ** Poor VBA programming was the cause, not data validation
Bookmarks