CODEWITHSUNDEEP
excelexcel-2007vba
user2123672
user2123672

Reputation: 21

VBA for determining chart type by number of rows in table

I'm trying to write some VBA that will automatically produce a certain type of chart based on the number of rows in a table. I'm using an IF loop, as below, based on a lastrow variable. i have scrolled through the code using F8 and the lastrow variable is registering correctly but this doesn't impact on the type of chart appearing - it is always a column chart, which i guess is the default setting... Any help greatly appreciated.

Segment of code:

With Worksheets("TableScores")
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
            ActiveSheet.Shapes.AddChart.Select
            If lastrow <= 3 Then
            .ChartType = xlBar
            Else:
            .ChartType = xlLine
            End If
End with

Upvotes: 0

Views: 506

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

(Untested)

With Worksheets("TableScores")
Dim cht as Chart
    Set cht = ActiveSheet.Shapes.AddChart()

    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    If lastrow <= 3 Then
        cht.ChartType = xlBar
    Else
        cht.ChartType = xlLine
    End If
End with

Upvotes: 4

Related Questions