Expanding Excel Ranges

How does one go about expanding a defined Excel Range? I have it defined as:

Code:

---

Dim TempRange As Excel.Range = Globals.Sheet1.Range("E4:P4")

---

Now what I want to do is add items into that range. In actuality, I need to add "P4" at the beginning, and "E4" at the end of TempRange, keeping all of the items in the original TempRange as well (two items on top of the total cells in TempRange). Is there an easy way to assign an array of values and put it back into an Excel.Range object?

Using VS 2008, Excel 2007, VSTO.

"E4:P4" are 12 items... I want a range of 14 items, with P4 at the beginning and E4 at the end... with the original 12 items in between.

Well when I try that, it still doesn't do what is intended.

Code:

---

Dim TempRange As Excel.Range = Globals.Sheet1.Range("P4,E4:P4,E4")

---

The E4:P4 range actually gets plotted first, then P4 and E4.

Yes, it does show 14 items, but the problem is that I am trying to chart these values. When you try to add that range to a series, it does show the 14 values, but the "E4:P4" selection is always plotted first, even though "P4" was first in that selection string.

Code:

---

        Dim TempRange As Excel.Range = Globals.Sheet1.Range("P4,E4:P4,E4")
        Dim MySeriesColl As Excel.SeriesCollection = MyChart.SeriesCollection
        Dim TempSeries As Excel.Series = MySeriesColl.Add(TempRange)
        TempSeries.Name = "Temperature"
        TempSeries.AxisGroup = Excel.XlAxisGroup.xlPrimary

---

No matter what you put for the range, the "E4:P4" part is plotted first, with the single "P4" and "E4" values at the end. I want them in the order specified, and it doesn't want to work grrrrrrrr

you can use join to combine ranges
something like
set myrange = join(myrange, range("f7:g99"))
for this example myrange must be an existing range

Use different method and syntax that may help:

Code:

---

Dim MySeriesColl As Excel.SeriesCollection = MyChart.SeriesCollection
Dim TempSeries As Excel.Series = MySeriesColl.NewSeries
TempSeries.Name = "Temperature"
TempSeries.Values = "=(Globals.Sheet1!R4C16,Globals.Sheet1!R4C5:R4C16,Globals.Sheet1!R4C5)"
TempSeries.AxisGroup = Excel.XlAxisGroup.xlPrimary

---

R4C16 is "P4", R4C5:R4C16 is "E4:P4", R4C5 is "E4"