Need to find beginning and ending points of an Excel Range in C#

Question

I'm trying to read an excel file from C# using COM, and can get it opened and loaded just fine. However, I don't want to use all of the data on the sheet (it expands monthly), just a certain subset that starts below the top of the sheet (row 3 for headers, row 4 for data) and goes to the end. I can currently get a range representing the entire set of data as Excel.Worksheet.UsedRange, but next I need to either manipulate this down to the desired range, or (preferably) find the end point to pass into another range for my actual data retrieval. Can Anyone tell me how to do either of these? Thanks.

Answer 1

I am not sure what you are trying to do. But here are some examples.

Assume I have the following range:

Excel.Worksheet sheet = this.Application.ActiveSheet as Excel.Worksheet;
Excel.Range range = sheet.get_Range("A1", "B5") as Excel.Range;

To Move your Range Down by n-number of row:

int n = 1;
int rows = range.Rows.Count;
int cols = range.Columns.Count;

Excel.Range newRange = range.get_Offset(n, 0).get_Resize(rows-n,cols);
newRange.Select(); //will select the new range will be 1 row lower

To Move your bottom row up

Excel.Range newRange = range.get_Resize(rows-n,cols);
newRange.Select(); //will select the new range will be 1 row higher

I assume you can figure out how to move it side to side.

get_Offset() will move the whole range over and then you need to resize the range.

EDIT: Now that i know what you want.

To select the Last Cell:

Excel.Range lastCell = range.Cells[rows, cols] as Excel.Range;
lastCell.Select();

Now you can use your own starting point like so:

Excel.Range newRange = sheet.get_Range("B1", lastCell);
newRange.Select();

Answer 2

Ok, found an answer (after nearly 3 hours total searching, asked here 2 hours in), so will post here for others.

Excel.Range urange = (Excel.Range)xlWorkSheet.UsedRange; // gives us the actual range<br>
string used = urange.get_Address(false, false, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

From the MSDN:

public string get_Address (
    [OptionalAttribute] Object RowAbsolute,
    [OptionalAttribute] Object ColumnAbsolute,
    [OptionalAttribute] XlReferenceStyle ReferenceStyle,
    [OptionalAttribute] Object External,
    [OptionalAttribute] Object RelativeTo)

which apparently the first two are true/false flags, the next is defined as an Microsoft.Office.Interop.Excel.XlReferenceStyle object, and I'm guessing the External is either a reference to an external file, or a flag of some sort. RelativeTo, I can only guess that it refers to an arbitrary position defined, maybe a range object, maybe a string. Unfortunately, the MSDN is extremely sparse on this topic, so I'm just guessing here and posting my guesses. However, using this code as I've posted, I'm able to retrieve the total used as "A1:B245" which gives me exactly what I want, and I can then create a new range by extracting the second part and can then continue on.

Answer 3

You should be able to use the Range.Row, Range.Rows.Count, Range.Column, and Range.Columns.Count properties to get the start and end of your range like so:

Dim used As Range, first As Range, last As Range
Set used = Sheet1.UsedRange
Set first = Sheet1.Cells(used.Row, used.Column)
Set last = Sheet1.Cells(used.Row + used.Rows.Count, used.Column + used.Columns.Count)

MsgBox ("First: " + first.Address + " Last: " + last.Address)

That sample code is in VBA, but all of those functions should be available in C# with COM.

Answer 4

From MSDN:

"Use the End property, along with a value from the XlDirection enumeration (xlUp, xlToRight, xlToLeft, xlDown), to retrieve a range that represents the cell at the end of the region, as if you'd pressed the key described by the enumerated value;"

rngDown = rng.get_End(Excel.XlDirection.xlDown);