▸ ▸ ▸ » VBA Code for Creating a Chart

VBA Code for Creating a Chart

Asked Feb 10, 2010, 11:24 AM — 1 Answer

Ok, here's my problem.

I have built a macro that creates a chart for me from certain columns of data. The columns are always the same ones and the data I contemplate always begins in the same row but the end data is a variable. Also, the last row always contains a Totals Row which is something that I want to exclude from the Chart.

The code I have looks like this for the chart range:

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A8:A35,G8:G35,U8:U 35"),

I would always be pointing to the same columns: A, G, & you and I would always begin the data on Row 8. I have to manually change my code though to get the last row of data. In this case, row 35 is the last row of data I want to include in my Chart. In reality, there are 36 rows worth of data with the last row being the Totals Row, which as I said would not be included in the chart.

How do I make the ending row a variable and subtract the totals row from the SourceData? Something life an offset?

Thanks.

Search this Thread

Share |

1 Answer

JBeaucaire Posts: 5,377, Reputation: 5041

Software Expert

Feb 10, 2010, 07:47 PM

Declare a "Last Row" variable, solve it, then insert it into your charting command:

Code:

Dim LR As Long:     LR = Range("A" & Rows.Count).End(xlUp).Row - 1

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A8:A" & LR & ",G8:G" & LR & ",U8:U" & LR)

Helpful (1)

Not your question? Ask your question View similar questions

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

Add your answer here.

Check out some similar questions!

VBA code [ 2 Answers ]

Once the month ends, cell "A1" must tick over to zero, where "A1" is used by me as a counter to show how much has been deliverd to date for the month.

VBA Code to Save from Microsoft Outlook [ 5 Answers ]

Hello - I hope my problem is a simple one. What I am attempting to do is create a VBA macro that will allow me to select an e-mail from my Outlook Inbox and save that e-mail (as an Outlook item) to a specified driveC:\Outlook Items\ I cannot record a macro in Outlook so I am stumped as to...

VBA Code for adding an autonumber primary key [ 2 Answers ]

Can anyone provide code for adding an autonumber field to an existing database and then making that field the primary key? Thank you.

Creating a Chart in Excel by including values in X and why Axis [ 2 Answers ]

1) I want to create a chart using the data in my excel sheet. 2) Data in Row 1 should become the x-axis labels in a chart and data in Row 2 should be the y-axis labels and the only data range actually in the chart. 3) When I highlight data in Rows 1 and 2 then click on Chart Wizard both data ...

View more Spreadsheets questions Search