Named Range for Column?

TimmiT

Board Regular
Joined
Dec 15, 2006
Messages
77
I have a sheet with tabular data. I graph the data, one column per chart on a separate sheet. Since the amount of data changes as a project progresses, I have made a macro to change the amount of data shown:

Code:
'
Dim LR As String
Dim LRV As String
Dim LRR As String

LR = Sheets("Gradation and AC").Range("T1")
LRV = Sheets("VMA").Range("T1")
LRR = Sheets("Test Ranges").Range("w1")

ScreenUpdating = False

Sheets("Charts").ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = "='Gradation and AC'!$A$18:$A$" & LR
ActiveChart.SeriesCollection(1).Values = "='Gradation and AC'!$F$18:$F$" & LR

Sheets("Charts").ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).XValues = "='Gradation and AC'!$A$18:$A$" & LR
ActiveChart.SeriesCollection(1).Values = "='Gradation and AC'!$g$18:$g$" & LR

This continues through each of the columns of data. I have coworkers that like to use workbooks that they know nothing about, and love to delete/insert rows and columns. Is there a way to use a named range, that still varies with the data to make this more foolproof?

Also, if a chart is removed, is there an easy way to have the macro skip it vs. encounter an error?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

TimmiT

Board Regular
Joined
Dec 15, 2006
Messages
77
Thanks, I think I can work with that to achieve the missing chart aspects. Anyone have ideas on how to keep the correct data on the chart if a column is added or removed, and still keep the variability of the column length?

Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,540
Messages
5,529,434
Members
409,876
Latest member
Akash Yadav
Top