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 to create the graph. in an external Excel spreadsheet.

Juan VelasquezAsked:
Who is Participating?
Juan VelasquezAuthor Commented:
Here is the code that I came up with and so far it seems to work well
 If xlDataTable.Columns.Count > colCharsetLen Then
                            finalColLetter = colCharset.Substring( _
                             (xlDataTable.Columns.Count - 1) \ colCharsetLen - 1, 1)
                        End If
                        finalColLetter += colCharset.Substring( _
                      (xlDataTable.Columns.Count - 1 + intH) Mod colCharsetLen, 1)
                        excelRange = String.Format("A1:{0}{1}", finalColLetter, xlDataTable.Rows.Count + 1)
                        '****The following code is used to create variables that will be used in the Create Graph section****
                        'Creates an array to hold the upper and lower limits of the range of the excel table
                        Dim strChartRange As String() = excelRange.Split(CChar(":"))
                        'Stores the first cell in the range
                        strFirstCell = strChartRange(0)
                        'Stores the last cell in the range
                        strLastCell = strChartRange(1)

                        'MessageBox.Show(Mid(strLastCell, 1, Len(strLastCell) - 1).ToString)
                        .SeriesCollection(1).XValues = "=Sheet1!$K$1:$" & Mid(strLastCell, 1, Len(strLastCell) - 1) & "$1"

Open in new window

Try this code snippet:
Range(Selection, Selection.End(xlToRight)).Select

Open in new window

Juan VelasquezAuthor Commented:
I'm creating the chart via automation from within  When I tried the code, stating that Selection was not declared
Juan VelasquezAuthor Commented:
Re "I'm creating the chart via automation from within  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 VelasquezAuthor Commented:
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

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

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.