+ Reply to Thread
Results 1 to 4 of 4

Condtionnal ClearContent Rapidity Issue in Large Table

  1. #1
    Forum Contributor
    Join Date
    08-28-2015
    Location
    Montreal, Canada
    MS-Off Ver
    2013 on PC, 2011 on MAC
    Posts
    158

    Condtionnal ClearContent Rapidity Issue in Large Table

    [NOTE: Turns out that didn't work, but explains the situation I'm going through]

    Hey everyone,

    This community has brought me so much in terms of answers and feedback through the last several years, I thought I would share a quick story, as it might help other 'newbies' just like me, who might be dealing with a similar issue in the future.

    (NOT SO) QUICK BACKSTORY
    So I've been working on V2 of a tool to acquire and analyse Stock Data from the web. Our REFRESH VBAs are pretty much working the same, and begin by clearing a few designated columns (in our case BN:CE) so that we won't use old data thinking it's been replaced by the updated one, using two IFs statement (in our case "X" in Column N and BL). That part of the code looks like this:

    Please Login or Register  to view this content.
    However, eversince I've started working on V2, the 'Clear Data to be Replaced' part as been an issue. Our REFRESH table is 4451 rows long, and while it was running fine at the very beginning, it became problematic right after I starter working on a new tab. I noticed it started to take more and more time to perform that ClearContents, to the point where it would take over 40 minutes to clear the whole table. And no matter what I tried (granted, I'm not the best coder around), I just couldn't seem to improve those performances. The new tab I was working on at the time is pretty heavy in terms of conditional formatting, objects being used as buttons...and for layout purposes, I had to build it with 286 columns, 0.5 wide, and merge everything up (I know...merged cells are the devil).

    So I started deleting some objects. Didn't improve anything. Removed all the conditional formatting, again, no impact. I un-merged all the cells, still nothing. That tab used buttons that are in fact objects linked to a cell (as a way to apply conditional formatting to obejct). And, while working on the layout of the page, I had to create, then delete, then recreate them many (MANY) times. So I thought: "All right, maybe there's some kind of cache in Excel that stored all those images that I'm not using anymore, and this is causing it to be slower than it should be". But couldn't find anything on the internet about that, other than a suggestion to turn off the recently open pages, which didn't help.

    So I started everything from scratch...about two-weeks and a half of work in the bin. Thinking I'd create those linked image at the very end only, and make sure not to create more than I would actually use. It took me 4 days to remake everything. Ran some REFRESH at various point...no issue. Until I started to work on that pesky tab again. Then, the same issue presented itself. So I said to myself: "Alright, instead of clearing it line-by-line for now, I'll just wipe-out the entire BN:CE columns in one fell swoop for our FULL REFRESH (that's what it was doing anyway), and I'll cross that river once I'll start working on targetted refresh", thinking it might be better if I'm only asking it to clear a few hundred lines instead of the whole 4451 rows.

    ...and while it was indeed (a lot) quicker, it still took around 30-40 secs just to clear the columns (for about 80-100 lines that met the two IFs criterias). But for a FULL REFRESH, I was back at 30-40 mins. Which is an eternity, especially when dealing on the Stock Market.

    So I did more testing. Hid some tabs that we didn't use as much. Removed the linked object buttons, and replaced them with 'invisible' ones placed over a cell that has conditional formatting. Placed the REFRESH button on a different (and empty) tab. Tried removing all the conditional formatting again. Un-merged all the cells. Nothing worked. And I thought "Alright, well, this tab is doomed...and I've just lost 4 days for nothing".

    THE SOLUTION
    I thought it was ridiculous to even try, to be honest. But that's what solved the problem.

    Like I said before, our REFRESH table is 4451 rows long...spreading over 109 columns wide. And while we do need all of those columns...they don't need to be visible. In fact, that whole tab is hidden, and just running in the background. We only access it for maintenance purposes. So I started hidding the columns, only keeping a few unhidden:
    B:M - Stock Infos (Symbol, Name, Sector, Index, etc...)
    N - First IF criteria
    BL - Second IF criteria
    BN:CE - Stock Data (Data collected from the internet by the first VBA)
    CS:DF - Stock Data (Data collected from the internet by the second VBA)

    So 57 columns hidden. Not deleted, just hidden.

    Clicked on FULL REFRESH (the one that took 30-40 to ClearContent). The whole table cleared, according to the two IFs criterias...in 5 seconds.

    So the issue that prevented the VBA from running smoothly and quick wasn't the absurd number of buttons, the sheer volume of conditional formatting or even the merged cells o the new tab (although all those things probably contributed)...but the fact that columns, in a very large table on an hidden tab...were visible. It didn't matter that the VBA itself wasn't even looking at those columns throughout the whole procedure, that simple fact that they were visibly 'in the way' made it tough to handle apparently.

    Why am I sharing this ?

    Well, like I said, this community has given me so damn much help/input these past few years...
    If sharing this story helps anyone saving 4-days worth of work, well...glad I could contribute to giving you a weekend free !
    I'm off to enjoy mine now.

    [Mods: Feel free to move this to a more appropriate place on the forum of deleted if this isn't viewed as 'Excel General' related topic]
    Last edited by KomicJ; 09-26-2020 at 12:49 AM. Reason: Changing Title and Put addionnal note at the beginning...

  2. #2
    Forum Contributor
    Join Date
    08-28-2015
    Location
    Montreal, Canada
    MS-Off Ver
    2013 on PC, 2011 on MAC
    Posts
    158

    Re: Table length affecting Performances ? Here's the first thing you should try...

    Well, that excitement was short-lived...turns out it wasn't it, as my CleanContent are back to the original lenght. Grrr....
    ...not sure what to do with this thread now.

    Leave it open for some possible feedback as to what the issue could be, or lock and ask the mod to delete it.

    So Mods, feel free to handle it the way you see fit.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,030

    Re: Table length affecting Performances ? Here's the first thing you should try...

    Leave it here, however please change the thread title of the opening post to a suitable question.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Forum Contributor
    Join Date
    08-28-2015
    Location
    Montreal, Canada
    MS-Off Ver
    2013 on PC, 2011 on MAC
    Posts
    158

    Re: Table length affecting Performances ? Here's the first thing you should try...

    Thanks Ali, title change done.

    Update #1
    ...after more testing, it looks like the issue might have more something to do with the REFRESH tab itself being hidden than anything else. With the tab visible, ClearContents are as fast as ever (not even 3 seconds, even for clearing the whole 4451 lines). With the tab hidden...now I'm back to longer ClearContents (depending on how many elements meet the two IFs criterias). I probably left the tab visible previously, after I hid the columns...because now, I'm testing it with all the columns visible and the tab unhidden, and it's working flawlessly.

    It's not ideal, as the screen flickers (during the copy/pastespecial segment of the code). And that flicker makes it a bit unstable (Excel sometimes becomes unresponsive...but not all the time).
    I tried placing a ScreenUpdating (False/True) at different point of the code, but it caused Excel to crash everytime.

    I'll try a few other things. Meanwhile, if you guys have any suggestion, I'm all eyes and ears.

    Update #2
    Alright, did more testing with ScreenUpdating, as that flicker really annoys me. I was able to make it work, but only if I avoid putting Application.ScreenUpdating = True at the other end of the code (read somewhere that, while it is good coding practice, turning it back on wasn't necessary). However, doing so caused another problem: I have a counter running through Application.StatusBar. That counter stops running as soon as I click on another window, or use ALT-TAB to do something else (our FULL REFRESH can take up to 2h30, so) and Excel becomes unresponsive. Without the Application.ScreenUpdating = False, Excel also goes 'unresponsive', but I can still see that counter run up, and data is always good at the end.

    So looks like I'm stuck between two bad apples. I either get a flicking screen or I lose the counter. So I settled on the first one, flicker it is.

    I'll let the thread open for 24 hours, just in case someone would like to suggest something I haven't tried out yet. But as of right now, I think I've tried all I can based on my own knowledge.
    Last edited by KomicJ; 09-26-2020 at 04:07 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Move a table without affecting the formula that depends on it
    By Mongoloid in forum Excel General
    Replies: 5
    Last Post: 05-20-2016, 05:04 PM
  2. Writing macros to calculate mutual fund performances
    By danukachathu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-02-2016, 07:18 PM
  3. [SOLVED] insert line without affecting adjacent table
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-02-2015, 08:45 AM
  4. [SOLVED] rank employees based on performances
    By tlacloche in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 10-10-2013, 10:53 AM
  5. Possible to link Table length to Pivot table length?
    By saber0091 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-04-2013, 05:36 PM
  6. Re-sort table without affecting original table?
    By jfrog in forum Excel General
    Replies: 1
    Last Post: 01-30-2013, 01:19 PM
  7. Replies: 4
    Last Post: 02-07-2011, 11:59 AM

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