VBA Charting issue

[ny156uk]

Hi,

I have about 10 graphs, each of which are powered by data held on a specified 'graphdata' page. The data is regularly changing length (i have a max of 12 rows, but sometimes there may only be 6). The graph currently is set default to plot all 12 rows - resulting (sometimes) in there being several blank rows of data on the graph.

So far I have tried something like this:

--Code starts--
Dim GraphSelect, ChartName, GraphName As String
Dim GraphArea As Range

Sheets("ExcelData").Select

GraphArea = Range("A19:F31")
ChartName = "VolRec"

Sheets(GraphSelect).Select
ActiveSheet.ChartObjects(ChartName).Select
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("Exceldata").Range(GraphArea), PlotBy:=xlColumns
--code ends--

The range I specified (a19:f31) is something I want the code to manually work out. It will always start A19 and end F something, but it needs to know how 'deep' to plot the graph.

The error I receive is "object variable or With block variable not set".

In the end I want to be able to 'loop' through the process and have this move onto the next chart and the next data-series.

Any help would be greatly appreciated - i'm the only person in the office today with any of this sort of knowledge and it's rather frustrating.

 

[Zwahlav]

Hello,
for this purpose I would use something like this:
GraphArea = Range("A19:F" & Range("A19").End(xlDown).Row)

I´ve written this just by hearth, but I think it should work.

Also:
You can try to start the macro recorder, then select the cell "A19" then press Shift+Ctrl+<down arrow> simultaneously, then stop the macro recorder and have a look on the recorded piece of code.

 

[Zwahlav]

Also:
You can try to start the macro recorder, then select the cell "A19" then press Shift+Ctrl+(down arrow) simultaneously, then stop the macro recorder and have a look on the recorded piece of code.

 

[tusharm]

You may want to go the non-VBA route. See
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

Hi,

I have about 10 graphs, each of which are powered by data held on a specified 'graphdata' page. The data is regularly changing length (i have a max of 12 rows, but sometimes there may only be 6). The graph currently is set default to plot all 12 rows - resulting (sometimes) in there being several blank rows of data on the graph.
{snip}