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:U35"),

I would always be pointing to the same columns: A, G, & U 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.

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



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)