CODEWITHSUNDEEP
excelvbagraphchartsscatter-plot
JetskiS
JetskiS

Reputation: 133

Create a graph that includes all columns until the last column

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.

Upvotes: 1

Views: 41

Answers (1)

Michal
Michal

Reputation: 6064

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

Upvotes: 1

Related Questions