excelvbagraphchartsscatter-plot

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.


Solution

  • 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