Setting the range of a chart in VBA as a variable

Question

I am trying to set the define a range variable and then use this range variable as a source for an Excel chart. But I am getting the Object Required Error when i run my Macro at Set rng =

 Sub temp3()
'
' temp3 Macro
'
    Dim rng As Range
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Set rng = Range(Selection, Selection.End(xlToRight)).Select

    ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=rng
End Sub

Try it without the .Select method at the end, i.e. Set rng = Range(Selection,Selection.End(xlToRight)) — user2731076
– user2731076, Commented Jan 2, 2018 at 19:35

Scott Craner · Accepted Answer · 2018-01-02 19:45:05Z

2

Do not use .Select

 Sub temp3()
'
' temp3 Macro
'
    Dim rng As Range
    Dim shp As Chart
    With Worksheets("Sheet1") 'Change to your sheet
        Set rng = .Range("A1", .Range("A1").End(xlDown).End(xlToRight))

        Set shp = Charts.Add
        Set shp = shp.Location(Where:=xlLocationAsObject, Name:=.Name)
        With shp
            .ChartType = xlLineMarkers
            .SetSourceData rng
        End With
    End With
End Sub

edited Jan 2, 2018 at 19:45

answered Jan 2, 2018 at 19:22

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rush · Accepted Answer · 2021-06-25 22:09:25Z

0

You can also create named range and set the data source of chart to named range. you will then use vba to change the range of the named range. That method may be simpler. See How do I redefine a named range with VBA? on how to change named ranged by vba

answered Jun 25, 2021 at 22:09

Rush

816 bronze badges

Collectives™ on Stack Overflow

Setting the range of a chart in VBA as a variable

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related