+ Reply to Thread
Results 1 to 3 of 3

Slow for each check takes 6 minutes

  1. #1
    Registered User
    Join Date
    03-14-2010
    Location
    Scotland
    MS-Off Ver
    Excel 2003 + 2007
    Posts
    10

    Lightbulb Slow for each check takes 6 minutes

    THOUGHTS:
    1.) Is there a better way of doing the entire project?
    2.) Would it work better if the data were split into 253 separate sheets each with one column and the match done against sheet names?
    3.) Would it work better if the data were split into 253 separate text files each with one column, the match done against names and then that text file imported?
    4.) Could it all be done using something like (with column count rows)?

    STRUCTURE:
    The workbook structure consists of:
    SHEETS Master - where the output is presented.
    SHEETS Country - just holds the list of 253 entries for the drop list in SHEETS master.
    SHEETS Data - holds all the data in a table with 254 columns varying in size from 1 cell to 44090 cells
    SHEETS OffsetData - this is only being used to output the offset data whilst I am trying to debug the coding.
    SHEETS CleanUpData - this is only being used to output the cleaned up data whilst I am trying to debug the coding.
    Full workbook size without populating SHEETS Master is 7,752 KB (7.7 MB)
    Full workbook size with SHEETS Master populated with 44090 cells in column B is 25,742 KB (25.7 MB)

    FURTHER REFERENCE:
    http://www.excelforum.com/excel-gene...r-columns.html

    ISSUES:
    If I use the VBA code as shown Ref: Code A then the output to SHEETS Master takes up to 2 seconds,
    however it outputs a lot of 0(zero) values in SHEETS Master Column B which in turn causes unnecessary data in
    SHEETS Master Columns D,E,F,G as seen in Ref: Fig 1

    If I use the VBA code as shown Ref: Code B then the output to SHEET Master takes up to 6 minutes
    however it outputs no 0(zero) values in SHEETS Master Column B which in turn causes virtually no unnecessary data in
    SHEETS Master Columns D,E,F,G as seen in Ref: Fig 2

    The code difference is given in Ref: Code C

    Ref: Code A
    Please Login or Register  to view this content.
    Ref: Fig 1
    Fig 1.gif

    Ref: Code B
    Please Login or Register  to view this content.
    Ref: Fig 2
    Fig 2.gif

    Ref: Code C
    Please Login or Register  to view this content.
    RESOLVE:
    ???
    Last edited by wolfen; 03-02-2012 at 11:24 AM.

  2. #2
    Forum Contributor darknation144's Avatar
    Join Date
    01-24-2012
    Location
    London
    MS-Off Ver
    Microsoft Excel 365 MSO
    Posts
    555

    Re: Slow for each check takes 6 minutes

    http://www.excelforum.com/excel-gene...readsheet.html

  3. #3
    Registered User
    Join Date
    03-14-2010
    Location
    Scotland
    MS-Off Ver
    Excel 2003 + 2007
    Posts
    10

    Re: Slow for each check takes 6 minutes

    Quote Originally Posted by darknation144 View Post
    Hi thanks for the reply, I had seen that thread, however it does not seem to be relevant to this issue as that post is for removal of images, I have no images just data.
    I understand that manipulation of image data can be processor intensive, therefore the solution given in that thread would be relevant to that thread unfortunately I do not think it is relevant to this thread.

+ 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