+ Reply to Thread
Results 1 to 3 of 3

Performance issue on large spreadsheet

  1. #1
    Registered User
    Join Date
    06-10-2011
    Location
    Mansfield, England
    MS-Off Ver
    Excel 2007
    Posts
    10

    Performance issue on large spreadsheet

    Hi all,

    I have a spreadsheet that has a large number of columns, and currently caters for a maximum of 9999 rows of "user" data. Now because of performance issues, I am thinking that I can chnage the sheet to only cater for a few rows of data initially, and then expand to cater for however many rows the user enters into the sheet.
    However, one of the problems I have is that many of the cells have data validation enabled, or a drop down list of permissible values, or are conditionally formatted. So at the moment, all of these things are applied to 99999 rows.
    If I reduce the number of rows to just a few initially, I am wondering what might be the best/most performant way of adding these validations to the rows the user adds?
    Although a user can enter a new row manually, they are also just as likely to copy and paste 3000 rows from another spreadsheet and then paste them in. So if they do that, should I write some code that looks at the used range of the data and adds the appropriate validation to each cell?

    Doing it this way does not sound very efficient to me, but as it stands, the current way of having all these validations already there on 9999 rows is also making using the sheet a nightmare.

    Any ideas?

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Performance issue on large spreadsheet

    Hi edwar368,

    There are many things in VBA that can speed up code. See
    http://blogs.office.com/b/microsoft-...practices.aspx

    That said there are other things that slow down spreadsheets. Most of these are Volitile functions.
    http://www.decisionmodels.com/calcsecretsi.htm

    If you suspect Conditional Formatting is slowing things down, make a copy of your file and remove the CF from it and see if it really does speed things up. There are a lot of reports about CF slowing things down but not many work-a-rounds that I can find.

    There is a chance that you have hidden shapes or other things that are slowing things down.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    06-10-2011
    Location
    Mansfield, England
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Performance issue on large spreadsheet

    Thanks Marvin,

    Your links contain some useful things I didn't know about.
    Actually our spreadsheet is not using conditional formatting, in fact Conditional formatting is one of the ways I was thinking to improve the performance. A lot of validation is done via VBA when a user clicks a "Validate" button, but in addition to this, our sheet has 40 columns, with 99999 rows, with almost every cell having "data validation" in it as well. Some of these cells are also calculated by formula's.

    So with regard to bottle necks I am thinking the large amount of data with formulas in them is possible not helping, though the performance tip about switching off auto recalc during the VBA validation may go a long way towards helping here.
    Also my thoughts were that seeing as we are validating via VBA anyway, why have all of the in cell validation, so was thinking of removing that, though I am not sure, if I am actually jumping the wrong way on this one, should I be using the in cell Data Validation over VBA? But what happens when a user copies and pastes 3000 rows instead of manually entering the data?

    Another thing the VBA validation does is colour the interior of the cells found to be in error so columns can be filtered by colour. This is where I am thinking of using Conditional formatting to replace the VBA code (though, I am struggling to be able to count the number of errors but that's a separate post)

    Thanks again for those good links

+ 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