MS Excel Auto-Graph Macro

Posted on 2011-10-06
Last Modified: 2012-05-12

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
Question by:WTC_Services
    LVL 85

    Accepted Solution

    See attached file wherein I have create a named range called Categories defined as:
    and assigned that as the XValues for the series. As you add more data, the series expands, and as you remove it it contracts.

    LVL 19

    Expert Comment

    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'
    LVL 85

    Expert Comment

    by:Rory Archibald
    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.

    Author Comment


    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
        ActiveChart.ChartType = xlLineMarkers
        ActiveChart.SeriesCollection(1).Name = "='DC WR'!$D$15"
        ActiveChart.SeriesCollection(1).Values = "='DC WR'!DCA"
        ActiveChart.SeriesCollection(2).Name = "='DC WR'!$I$15"
        ActiveChart.SeriesCollection(2).Values = "='DC WR'!DCB"
        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

    Author Closing Comment

    Thanks for the dynamic range help!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now