Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

MS Excel Auto-Graph Macro

Hi,

I'm trying to make a graph that automatically scales to the right size depending on what data I input in excel. So effectively it's x-axis should only be as long as i have values.

The graph is for a stanardized form where there could be anywhere between 5 and 25 input values and I want a graph to be automatically produced so that it is the right size, incorporating all values.

Thanks! experts-graph.xlsx
0
WTC_Services
Asked:
WTC_Services
  • 2
  • 2
1 Solution
 
Rory ArchibaldCommented:
See attached file wherein I have create a named range called Categories defined as:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$B:$B))
and assigned that as the XValues for the series. As you add more data, the series expands, and as you remove it it contracts.

HTH
Rory
experts-graph.xlsx
0
 
Arno KosterCommented:
1) click on the 'not like this' graph
2) click on the horizontal axis (0...20)
3) right-mouseclick on the axis and select 'format axis'
4) select 'automatic' instead of the manual value '20' for the maximum
5) press 'close'
0
 
Rory ArchibaldCommented:
That would only work because the chart has actually been set to use rows 2:7. If you set it to the whole data set, it will look like it does now.
0
 
WTC_ServicesAuthor Commented:
Rorya,

I've got the graph working really well now by using a Dynamic Range for my data thanks! I'm having some trouble now adding a title and axis labels to my chart?

This is my script:

    Sheets("DC WR").Select
    Range("B83").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "='DC WR'!$D$15"
    ActiveChart.SeriesCollection(1).Values = "='DC WR'!DCA"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "='DC WR'!$I$15"
    ActiveChart.SeriesCollection(2).Values = "='DC WR'!DCB"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).Name = "='DC WR'!$N$15"
    ActiveChart.SeriesCollection(3).Values = "='DC WR'!DCC"
   
    With ActiveChart.Parent
        .Left = 5
        .Width = 543
        .Top = 1040
        .Height = 370
    End With

thanks WTC
0
 
WTC_ServicesAuthor Commented:
Thanks for the dynamic range help!

WTC
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now