Interop - Setting a range for an Excel chart to an entire row

Question

How do I set the source data of an excel interop chart to several entire rows?

I have a .csv file that is created by my program to display some results that are produced. For the sake of simplicity let's say these results and chart are displayed like this: (which is exactly how I want it to be)

enter image description here

Now the problem I am having is that the number of people is variable. So I really need to access the entire rows data.

Right now, I am doing this:

var range = worksheet.get_range("A1","D3");

xlExcel.ActiveChart.SetSourceData(range);

and this works great if you only have three Persons, but I need to access the entire row of data.

So to restate my question, how can I set the source data of my chart to several entire rows?

I tried looking here but couldn't seem to make that work with rows instead of columns.

Try this link it actually talks about `Rows` http://stackoverflow.com/questions/12363163/fastest-way-to-get-an-excel-range-of-rows — MethodMan, Mar 20 '13 at 18:45
@Jordan: See if the `CurrentRegion` property helps as explained below. — shahkalpesh, Mar 20 '13 at 18:52

shahkalpesh · Accepted Answer · 2013-03-20T18:54:13.107

3

var range = worksheet.get_range("A1").CurrentRegion;

xlExcel.ActiveChart.SetSourceData(range);

EDIT: I am assuming that the cells in the data region won't be blank.
To test this,
1) place cursor on cell A1
2) press F5
3) click on "Special"
4) choose "Current Region" as option
5) click "OK"

This will select the cells surrounding A1 which are filled, which I believe is what you are looking for.

The translation of that in VBA code points to CurrentRegion property. I think, that should work.

edited Mar 20 '13 at 18:54

answered Mar 20 '13 at 18:48

shahkalpesh

33,172
3
63
88

Yes! +1 for simplicity. Thank you! – Jordan Carroll Mar 20 '13 at 18:53
@JordanCarroll: Thanks. I am glad to be of help. – shahkalpesh Mar 20 '13 at 18:55

score 0 · Answer 2 · answered Mar 20 '13 at 18:45

Check Out the option Range.EntireRow I'm not 100% on how to expand that to a single range containing 3 entire rows, but it shouldn't be that difficult to accomplish.

Another thing you can do is scan to get the actual maximum column index you need (this is assuming that there are guaranteed to be no gaps in the names), then use that index as you declare your range.

Add Code

int c = 2;//column b
while(true)
{
    if (String.IsNullOrEmpty(worksheet.GetRange(1,c).Value2))
    {
        c--;
        break;
    }
    c++;
}

score 0 · Answer 3 · answered Mar 20 '13 at 18:48

Take a column from A to D that you're sure has no empty cells.

Do some loop to find the first empty one in that column and it will be one after the last.

Range Cell = SHeet.Range["A1"]; //or another column you're sure there's no empty data

int LineOffset = 0;

 while (Cell.Offset[LineOffset, 0].Value != "")  //maybe you should cast the left side to string, not sure.
{
 LineOffset++;
}

int LastLine = LineOffset - 1;

Then you can get Range[Sheet.Cells[1,1], Sheet.Cells[LastLine, 4]]

score 0 · Answer 4 · answered Apr 02 '13 at 12:28

Out of the box here, but why not transpose the data? Three columns for Name, Height, Weight. Convert this from an ordinary range to a Table.

When any formula, including a chart's SERIES formula references a column of a table, it always references that column, no matter how long the table gets. Add another person (another row) and the chart displays the data with the added person. Remove a few people, and the chart adjusts without leaving blanks at the end.

This is illustrated in my tutorial, Easy Dynamic Charts Using Lists or Tables.

Interop - Setting a range for an Excel chart to an entire row

4 Answers4