I want to create a graph in Excel using vba with a dynamically amount of columns. My partial current code is:
'Create chart
'Columns("A:DZ").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("Graphs!$A:$AL")
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Pressure over time"
With ActiveChart
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
.AxisTitle.Text = "Time [s]"
.MaximumScale = 120
End With
With .Axes(xlValue, xlPrimary)
.HasTitle = True
.AxisTitle.Text = "Pressure [bar]"
End With
.HasLegend = False
End With
However, the graph includes the columns A
until AL
. However, AL
is not my last column. I want my graph include the columns A
until lastcolumn-1
.
And then I want to create another graph that only includes the columns A and lastcolumn.
I am not sure how to adapt this current code. I tried Range("Graphs!$A"&(lastcol-1)
, but that obviously did not work. lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
is already created in my code, so that ony already works.
If something is not clear, please let me know.
Dim lastCol As Long
Dim chartRange As Range
' Get the last column in the "Graphs" sheet
lastCol = Sheets("Graphs").Cells(1, Columns.Count).End(xlToLeft).Column
' Set the range for the first chart (A to the second-last column)
Set chartRange = Sheets("Graphs").Range(Cells(1, 1), Cells(1, lastCol - 1)).Resize(Cells(Rows.Count, lastCol - 1).End(xlUp).Row)
' Create the first chart
ActiveChart.SetSourceData Source:=chartRange
' Do the rest of your code
' Your second chart
' Set the range to include only columns A and the last column
Set chartRange = Union(Sheets("Graphs").Range("A1:A" & Sheets("Graphs").Cells(Rows.Count, 1).End(xlUp).Row), _
Sheets("Graphs").Range(Cells(1, lastCol), Cells(Rows.Count, lastCol).End(xlUp)))
' Do the rest of your code