Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Fit line within the chart using Excel-VBA

Fit line within the chart using Excel-VBA

Fit line within the chart using Excel-VBA


I have the following data in sheet1 from A1 to B10

0 0
100 100
200 100
300 100
400 100
500 100
600 100
700 100
800 100
900 100

Used the following code to plot horizontal line in the chart1. But unable to fit the line within the chart.

Option Explicit

Sub Macro1()
' Macro1 Macro
' Macro recorded 5/17/2016 by Elogesh


Dim last_row As Integer
Dim i As Integer

last_row = Sheet1.Range("A1").End(xlDown).Row

For i = 1 To last_row - 1

ActiveSheet.Shapes.AddLine(Sheet1.Cells(i, 1), Sheet1.Cells(i,2), Sheet1.Cells(i+1, 1), Sheet1.Cells(i+1,2)).Select
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.ForeColor.RGB = RGB(50, 0, 128)
Selection.ShapeRange.Line.Weight = 3

Next i

End Sub

Kindly let us know how to fit the lines within the chart. Whether X, Y range for the chart can be specified prior to "addline", so that it can be autofit/scaled within the chart.


RE: Fit line within the chart using Excel-VBA

Your macro is creating an empty chart, then drawing some lines inside the chart, but they aren't chart lines, so there is no connection between the chart scale and the scale of the lines. Also the lines use screen coordinates, not chart coordinates, so the first line slopes down from the top-left corner, rather than sloping up from the origin.

It is possible to scale drawn lines with VBA, but if you are using a graph it is much easier to use the graph lines.

If you record a new macro, and select the data then insert a graph of the required type that will automatically scale the chart limits, just as it does when you create a graph manually.

If you really want to work with drawn lines rather than chart lines for some reason, search my blog for drawing with VBA and you should find something on plotting lines (and other shapes) from coordinates.

Doug Jenkins
Interactive Design Services

RE: Fit line within the chart using Excel-VBA


Doug made some good points. May I add an additional comment. Your table of source data appears to have no headings. Headings help to give your data context. In addition, I'd suggest using the Structured Table feature that Excel acquired in version 2007. If you link your chart to data in a Structured Table, then whenever you add or delete rows of data, your chart will respond accordingly.

To convert a table to a Structured Table: Insert > Tables > Table

Your Structured Table can be returned to a normal table at any time. Notice the context sensitive pop up in the Ribbon when you select in a Structured Table.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close