VB Dynamic Graph Excel Range Error

Hi experts,

I have a small vb script that I want to run to change the source data on a graph.  No matter what I try I can not get the graph to accept the defined range.  Here is the code:

intLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
        intLastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
        ActiveChart.SetSourceData Source:=Sheets("Chart Data").Range(Cells(5, 1), Cells(intLastRow, intLastCol))

Open in new window

The error is always shown on the last line.

Any help would really be appreciated.

Who is Participating?
Nico BontenbalConnect With a Mentor Commented:
Try this:
ActiveChart.SetSourceData Source:=Sheets("Chart Data").Range(Sheets("Chart Data").Cells(5, 1), Sheets("Chart Data").Cells(intLastRow, intLastCol))
Nico BontenbalCommented:
The Cells(5, 1) in your code refers to the cells of the active sheet. Being the Graph sheet, which doesn't have cells, hence the error.
GlenmoranUKAuthor Commented:
Fantastic..  That has had me going round in circles for a good couple of hours..  :o)

Many thanks for the code and more importantly the explanation.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.