Create Excel graph of columns uptill lastCol using vba

Question

I want to create a graph in Excel using VBA that contains column A up till the last column. The last column is determined with the line:

        Dim lastCol As Long, lastColLetter As String
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        lastColLetter = Split(Cells(1, Columns.Count).End(xlToLeft).Address, "$")(1)
        lastColLetter = Chr(34) & lastColLetter & Chr(34)

This lastColLetter prints for example "AL"

When creating a graph, I have the following code:

  ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
  ActiveChart.SetSourceData Source:=Range("Graphs!$A:$DZ")
  ActiveChart.ChartTitle.Select
  ActiveChart.ChartTitle.Text = "Pressure over time"

  With ActiveChart
    With .Axes(xlCategory, xlPrimary)
      .HasTitle = True
      .AxisTitle.Text = "Foaming time [s]"
      .MaximumScale = 120
    End With
    With .Axes(xlValue, xlPrimary)
      .HasTitle = True
      .AxisTitle.Text = "Pressure [bar]"
    End With
    .HasLegend = False
  End With

I want to replace the DZ line ActiveChart.SetSourceData Source:=Range("Graphs!$A:$DZ") with something like ActiveChart.SetSourceData Source:=Range("Graphs!$A:$" & lastColLetter), which does not seem to work. It results in an empty graph. How can I use lastCol while creating a graph?

You've used Cells to get the cell reference - two of these stuck together create a range. No need to figure out the column letter. You could use Range(Cells(1, 1).EntireColumn, Cells(5, ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column).EntireColumn) — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Oct 3, 2024 at 13:30
try to delete this line lastColLetter = Chr(34) & lastColLetter & Chr(34). lastColLetter is already a string — Black cat
– Black cat, Commented Oct 3, 2024 at 14:25

FunThomas · Accepted Answer · 2024-10-03 13:47:29Z

If you are programming VBA for Excel and you want to know the character(s) of a column, you can be almost 100% sure you are doing something the wrong way.

Also, if you are using something with Active (like in your case ActiveChart), code has at least a smell. Seems to me it was created with the macro recorder - that is notorious for creating bad code.

There are more than one way to do what you want, but the following can give you an idea:

Dim lastCol As Long
With ThisWorkbook.Worksheets("Graphs")
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

Dim sh As Shape
Set sh = ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers)
With sh.Chart
    .SetSourceData Source:=Cells(1, 1).Resize(, lastCol).EntireRow
    .ChartTitle.Select
    .ChartTitle.Text = "Pressure over time"

    With .Axes(xlCategory, xlPrimary)
        .HasTitle = True
        .AxisTitle.Text = "Foaming time [s]"
        .MaximumScale = 120
    End With

    With .Axes(xlValue, xlPrimary)
        .HasTitle = True
        .AxisTitle.Text = "Pressure [bar]"
    End With
    .HasLegend = False
End With

Collectives™ on Stack Overflow

Create Excel graph of columns uptill lastCol using vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related