Graphing help in excel vb

[AleemAM123]

I need some help with a graph. I am drawing a graph from the table on GCCTable. The graph uses column A and column H, the number of rows can change and be more/less. I want to draw a graph on this using vba, Is there a better way to do this other than the following:

Sub GrandCompCurve()
'
' GrandCompCurve Macro

'

'
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Dim StreamCount As Integer


'Sheets("GCCurve").Select
'ActiveWindow.SelectedSheets.Delete
'Sheets("GCCTable").Select


Range("A4").Select
'Worksheets("1cdu").Range("l25") = "=[" & FileName2 & "]Sheet1!R44C7"
'count the number of cells from A6 and below
Range(Selection, Selection.End(xlDown)).Select
'number of rows
StreamCount = Selection.Count


Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("GCCTable").Range("A4:H14"), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=GCCTable!R4C8:R" & StreamCount + 3 & "C8"
ActiveChart.SeriesCollection(1).Values = "=GCCTable!R4C1" & ":R" & StreamCount + 3 & "C1"
ActiveChart.SeriesCollection(1).Name = "=""GCC"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="GCCurve"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Grand Composite Curve"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Energy"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Shifted Temperature"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.Axes(xlCategory).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlNextToAxis
End With
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.PlotArea.Select
Selection.Interior.ColorIndex = xlNone
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
With Selection.Border
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With


With Application
.ScreenUpdating = True
.Calculation = xlCalculationManual
End With

End Sub



I would like to automate the graphs on sheet CCC as well. The number of rows can vary here as well.

Any help would be great, thanks.:friends:

[p45cal]

I don't think it's too bad, I've made some changes:

Sub GrandCompCurve()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim StreamCount As Long
Application.DisplayAlerts = False
On Error Resume Next
Sheets("GCCurve").Delete
On Error GoTo 0
Application.DisplayAlerts = True
With Sheets("GCCTable")
StreamCount = .Range(.Range("A4"), .Range("A4").End(xlDown)).Cells.Count
End With
With Charts.Add
.ChartType = xlXYScatter
.SetSourceData Source:=Sheets("GCCTable").Range("A4").Resize(StreamCount, 8), PlotBy:=xlColumns
With .SeriesCollection(1)
.XValues = "=GCCTable!R4C8:R" & StreamCount + 3 & "C8"
.Values = "=GCCTable!R4C1" & ":R" & StreamCount + 3 & "C1"
.Name = "GCC"
With .Border
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
.Location Where:=xlLocationAsNewSheet, Name:="GCCurve"
.HasTitle = True
.ChartTitle.Characters.Text = "Grand Composite Curve"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Energy"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Shifted Temperature"
.HasLegend = False
.PlotArea.Interior.ColorIndex = xlNone
With .Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlNextToAxis
.MinimumScale = 0
'many of the following can probably be omitted if they're the defaults:
'****************
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
With .Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
'****************
End With
With .Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
End With 'charts.add
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic 'changed
End With
End Sub Regarding the charts on CCC, do the same, record a macro adding the charts, post it here and I (or others) can streamline.
I'm off for some zzzzzs.

[AleemAM123]

This is great p45cal, I'll try doing the other graph and posting it here. I kept wondering why my calculation kept going to manual :doh:I forgot that I had copied that code ( .Calculation = xlCalculationAutomatic 'changed) from the top of another module and that I was supposed to change it back to automatic. Thanks!!