Graphing two cell ranges using Macro

**dami** · 06-30-2009, 04:46 PM

Hey,
I am trying to write a macro to graph two specified columns that has a constant starting row( for ex: A22 for x-axis and D22 for y-axis), but the ending row changes depending on how many data points are acquired in the test.

1. I pasted the code I got so far from Macro recording and me trying to edit it to make it universal (trying to apply it to different files) and I can't figure out the code to make it run the macro in the Active sheet of any workbook as I will be doing many tests.

2. is it possible to find the last row of data and graph the points up to that row? (for ex: Two Columns and starting row always equals to A22 and D22, and the macro should find the last row, say it found A3000 and D3000. Then graph the range A22:A3000 as x and D22:D3000 as y. )

hope it makes sense!

Code:

Sub graphing()


    Range("E22:E4147").Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmooth
    ActiveChart.SetSourceData Source:=ActiveSheet.Range("E22:E4147"), PlotBy:=xlColumns
    ActiveChart.SeriesCollection(1).XValues = _
        "=ActiveSheet!R22C1:R4147C1"
    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (S)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Voltage (V)"
    End With
    ActiveChart.PlotArea.Select
    Selection.ClearFormats
    ActiveChart.Legend.Select
    Selection.Delete
    ActiveChart.PlotArea.Select
    With Selection.Border
        .ColorIndex = 57
        .Weight = xlMedium
        .LineStyle = xlContinuous
    End With
    Selection.Interior.ColorIndex = xlNone
    ActiveChart.Axes(xlValue).Select
End Sub

**Andy Pope** · 07-01-2009, 05:54 AM

You can use the End method to locate last row with data.

Code:

Sub graphing()

    Dim lngLastRow As Long

    lngLastRow = Cells(Rows.Count, 5).End(xlUp).Row
    
    Range("E22:E" & lngLastRow).Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmooth
    ActiveChart.SetSourceData Source:=ActiveSheet.Range("E22:E" & lngLastRow), PlotBy:=xlColumns
    ActiveChart.SeriesCollection(1).XValues = _
        "='" & ActiveSheet.Name & "'!R22C1:R" & lngLastRow & "C1"
    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (S)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Voltage (V)"
        .PlotArea.ClearFormats
        .Legend.Delete
        With .PlotArea.Border
            .ColorIndex = 57
            .Weight = xlMedium
            .LineStyle = xlContinuous
        End With
    End With
    Selection.Interior.ColorIndex = xlNone
    ActiveChart.Axes(xlValue).Select
End Sub

**dami** · 07-02-2009, 10:23 AM

Thanks for the reply Andy, but it gives a run time error 438, "Object does not support this Property or Method"

When I go to Debug it, it highlights the following row:

Code:

 ActiveChart.SetSourceData Source:=ActiveSheet.Range("E22:E" & lngLastRow), PlotBy:=xlColumns

**Andy Pope** · 07-02-2009, 10:41 AM

The activesheet immediately after inserting a chartsheet is the chart sheet.
Chart sheets do not have cells or ranges.

So you need to create a references to the sheet

Code:

dim shtData as worksheet

set shtData = Activesheet

lngLastRow = shtdata.Cells(shtdata.Rows.Count, 5).End(xlUp).Row
    
    shtdata.Range("E22:E" & lngLastRow).Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmooth

ActiveChart.SetSourceData Source:=shtData.Range("E22:E" & lngLastRow), PlotBy:=xlColumns