Solved

Excel 2003 Make Graph on changing table

Posted on 2010-08-29
4
224 Views
Last Modified: 2012-06-21
Hi All,

I have a chart that is list a bunch of suff (obviously).  I have a visual basic script that filters this data down to four rows....

I then have a graph that is based on the current four rows.

My problem is that the rows may never be the same.  So I can not write something like:
    ActiveChart.SeriesCollection(2).Values = "='results'!$B$32:$B$35"

because the rows may be showing rows 131-134...

So my question is, is there a way to put the values of a series based on an updated table.. wonder if this makes sense...
0
Comment
Question by:sporenza
  • 2
4 Comments
 

Author Comment

by:sporenza
ID: 33554162
I have tried:

    ActiveChart.SeriesCollection(1).XValues = "=results!R2C1:R5C1"

This does not work either....
I get the error "Invalid Parameter"
0
 

Author Comment

by:sporenza
ID: 33554178
Actualy

ActiveChart.SeriesCollection(1).XValues = "=results!R2C1:R5C1"

Works but it is still A2:A5,

I want the first 5 visible rows...
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 33554462
Hello sporenza,

Excel will normally not plot data that is filtered out. In the attached file, the chart is built on all data in rows 1 to 13, but a filter is applied. Only the three visible rows are showing.

Click the chart, then click Tools - Options - Chart tab and tick "Plot visible cells only". This is independent of any VBA code, so, in your code you can simply address the whole table.

cheers, teylyn
Book1.xls
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now