Fit line within the chart using Excel-VBA
Fit line within the chart using Excel-VBA
(OP)
hi,
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
Charts.Add
Worksheets("Sheet1").Activate
last_row = Sheet1.Range("A1").End(xlDown).Row
For i = 1 To last_row - 1
ActiveWorkbook.Sheets("Chart1").Activate
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.
Thanks
Logesh
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
Charts.Add
Worksheets("Sheet1").Activate
last_row = Sheet1.Range("A1").End(xlDown).Row
For i = 1 To last_row - 1
ActiveWorkbook.Sheets("Chart1").Activate
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.
Thanks
Logesh





RE: Fit line within the chart using Excel-VBA
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
http://newtonexcelbach.wordpress.com/
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.
Skip,
Just traded in my OLD subtlety...
for a NUance!