+ Reply to Thread
Results 1 to 4 of 4

Excel worksheet with 5 data validated columns brings performance to a crawl. Why?

  1. #1
    Registered User
    Join Date
    02-28-2007
    Posts
    8

    Exl worksheet with 5 data validated columns crawls performance->VBA error caused it!

    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
    Last edited by pwhooftman; 03-09-2007 at 12:44 PM.

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    You might find an answer on this link?

    http://www.mvps.org/dmcritchie/excel/slowresp.htm
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    02-28-2007
    Posts
    8
    Thanks Oldchippy,

    Turns out that the problem is not in the data validation but in this piece of VBA in the worksheet. I can't figure out what makes Excel go to a crawl after this code has been run 20-40 times.

    Please Login or Register  to view this content.
    Last edited by pwhooftman; 03-09-2007 at 05:13 AM.

  4. #4
    Registered User
    Join Date
    02-28-2007
    Posts
    8
    I have reworked my code, and this works much better

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1