Changin x-axis labels in a graph

[GovNewbi]

When my program generates a graph it reads the values in a cell range I have called "projects" and it uses these values as the labels along the x-axis. the problem is that "projects" is a column in a table and it can change sizes, when it does there are sometimes blank spaces that the program is reading and labeling the x-axis with. This is realy juat a cosmetic problem but I was wondering if anyone knows how to fix it?

 

[SkipVought]

Hi,

I do not understand, because I cannot duplicate what you describe.

When I add new rows to the chart source data and adjust the Named Range, it displays the new labels and produces a new point in the series.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

Ok as an overview of my program the user inputs all sorts of information, that info goes into one table. The macro reads this table and formats a second table. There can be one of four formats depending on the user input. The macro then populates this table pulling information from other workbooks on this computer. Then the macro displays one of four graphs. You can run this over and over agian without closing the workbook. So if the user runs it and there are 5 projects the table will strech to fit 5 and the "projects" area is 5 cells. Then the user runs it again and only inputs 3 projects. Now the computer still has a table that is 5 rows and it reads the two blanks as part of "projects". Then this is graphed using

ActiveChart.SeriesCollection(1).XValues = Range("Projects").Value


I have tried using
ActiveChart.SeriesCollection(1).XValues = ws1.Range(ws1.Range("StProj"), ws1.Range("StProj").End(xlDown))

But then I cannot use the PlotBy later on. For some reason this deactivates that.

I hope this was more clear. I have a hard time explaining some of this stuff
Next r

 

[SkipVought]

Then the user runs it again and only inputs 3 projects. Now the computer still has a table that is 5 rows and it reads the two blanks as part of "projects". [/quote
Well THERE is your problem!!!

If the the user makes a change, then the resulting table ought to change, resulting in THREE ROWS, not FIVE!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

yes i know this is the problem what i don't know is how to fix it lol

 

[SkipVought]

How are you currently creating the chart Source Data Table?

I would definitely use MS Query, based on the user's criteria.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

The four graphs vary a little but here is the general idea. This isn't the whole code but I think this is the piece the problem is in...

ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Range("Graph2Area")
ActiveChart.SeriesCollection(1).XValues = Range("Projects").Value
ActiveChart.PlotBy = xlRows
For Each r In ws1.Range(ws1.Range("StProj"), ws1.Range("StProj").End(xlDown))
ActiveChart.SeriesCollection(i).Name = "='Sheet1'!$A$" & SerNum
i = i + 1
SerNum = SerNum + 1
Next r

 

[SkipVought]

That's all well and good.

This code does not address the problem if what is in the source data in not correct!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

k... i'm not sure how to address that.

 

[SkipVought]

MS Query!

You can query your own workbook. The SHEET that contains the data from which you create the chart SOURCE DATA, is the SHEET that will be the source for your query.

Data > Import External Data > New Database query...

Choose Excel Files* and drill down to your workbook. Select your SHEET and drag the fields you want to return, into your query. Specify your CRITERIA.

File > Return date to Microsoft Excel.

Once you have this QueryTable object on your sheet, you can use some simple code to change the CRITERIA, in order to return ONLY the data that the user intends to display in the chart.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

I have a feeling this is over my head cuz that was like a foreign language to me lol.

 

[SkipVought]

Depending on your requirements, the solution may be as simple as FILTERING your Chart Source Data using the AutoFilter.

That MAY be a better solution, again, depending your your requirements.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

YES the filtering worked!!! It actually solved all the cosmetic problems I was experiencing! THANK YOU!