+ Reply to Thread
Results 1 to 3 of 3

Deleting series from pivot chart

  1. #1
    Registered User
    Join Date
    07-01-2010
    Location
    Portland, OR, USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Deleting series from pivot chart

    Greetings,

    I have a macro that takes a range of data (timestamp in column A, machine error in column B), sorts it, makes a pivot table, and charts the data. The user decides beforehand how many errors should be shown on the chart. Then, it calls another sub which deletes all charted series except for the top n errors.

    My code does what I want it to do, but it takes a long time. The sorting and pivot table work takes just a second or two, but the process of deleting the undesired series takes 12 seconds.

    My question is this: do you know of any way to quickly remove series from a pivot chart? It would be faster if there were some command to clear all series, and then I could add back in the 5 that I want.

    Here is the offending code:
    Please Login or Register  to view this content.
    Sheet1.textbox1.value refers to a sheet which I have removed for this simplified version of the file; for now you could just substitute it with 3 or 5.
    The data in column F is basically just a list of series names, starting in row 3, sorted by frequency of the error, with the most frequent at the top.

    Again, the "offending code" does its job correctly. It's just too slow.
    I'm running this on a computer with 512 MB of RAM and GHz, so I'm curious to know how fast my macro runs on a faster computer.

    Thanks!
    -Joe

    Edit: forgot to attach the file. The macro to run is Macro1.
    Attached Files Attached Files
    Last edited by theradpotato; 07-09-2010 at 07:49 PM.

  2. #2
    Registered User
    Join Date
    07-01-2010
    Location
    Portland, OR, USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Deleting series from pivot chart

    I realized I could sort the pivot table and get the errors ranked by frequency from left to right. So now what I'm trying to do is simply use the left N rows of the pivot table as a graph. Of course the pivot chart wants to chart the entire table.

    I could copy and paste the values somewhere else and use that data to chart, but it runs into formatting issues since I'm organizing the pivot table by Day and then by Hour.

    I could chart all the data and manually remove the undesired series, but that's the same issue as before; it takes at least 10 seconds for each tool, and I want to run it for ~40 tools every morning.

    Any ideas?
    -Joe
    Last edited by theradpotato; 07-09-2010 at 02:20 PM. Reason: typo

  3. #3
    Registered User
    Join Date
    07-01-2010
    Location
    Portland, OR, USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Deleting series from pivot chart

    Aha -- there's a filter option "Top 10..." that allows me to pick the top N number of errors by error count. That's perfect.

+ 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