asked on

Setting XValues to a dynamic range

I have the following code that sets the x axis so that the x axis is set to the years located in "=Sheet1!$F$1:$N$1"
.SeriesCollection(1).XValues = "=Sheet1!$F$1:$N$1"
Now the range of years will always be in the first row (header row) and will start cell F1, however the number of years will be dynamic. So I need to set .SeriesCollection(1).XValues to a dynamic range. I am using automation from within vb.net to create the graph. in an external Excel spreadsheet.

gtgloner

Try this code snippet:

Range("F1").Select
Range(Selection, Selection.End(xlToRight)).Select

Open in new window

Juan Velasquez

ASKER

I'm creating the chart via automation from within vb.net. When I tried the code, stating that Selection was not declared

Juan Velasquez

ASKER

Re "I'm creating the chart via automation from within vb.net. When I tried the code, stating that Selection was not declared" Never mind I took care of it via
chartRange(excelApp.Selection, excelApp.Selection.End(excelApp.xlToRight)).Select()

Juan Velasquez

ASKER

No it doesn't work. I get the following error -Conversion from string "F1" to type 'Integer' is not valid. on line excelApp.Range(excelRange("F1")).Select()
At this point excelRange is set to A1:N7

excelApp.Range(excelRange("F1")).Select()
                    Dim rng As Excel.Range = excelApp.Range(excelApp.Selection, excelApp.Selection.End(excelApp.xlToRight)).Select()
                    .SeriesCollection(1).XValues = rng

Open in new window

ASKER CERTIFIED SOLUTION

Juan Velasquez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial