Need to find start and end points of Excel range in C #

Question

I am trying to read an excel file from C # using COM, and I can open it and load. However, I don’t want to use all the data on the sheet (it expands monthly), just a specific subset that starts below the top of the sheet (line 3 for headings, line 4 for data) and ends, Currently I can get a range. representing the entire data set, like Excel.Worksheet.UsedRange , but then I need to either change this value to the desired range, or (preferably) find the endpoint to go to another range for my actual data extraction. Can someone tell me how to do this? Thanks.

Answer 1

I'm not sure what you are trying to do. But here are a few examples.

Suppose 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 to n -number lines:

 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 

Move 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 that you can understand how to move it from side to side.

get_Offset() will move the entire range, 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 this:

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

Answer 2

From MSDN :

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

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

Answer 3

You can use Range.Row , Range.Rows.Count , Range.Column and Range.Columns.Count to get the beginning and end of your range as follows:

 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) 

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

Answer 4

Well, I found the answer (after almost three hours of a full search, I asked for 2 hours here), so post here for others.

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

From MSDN:
public string get_Address (
[OptionalAttribute] Object RowAbsolute,
[OptionalAttribute] Object ColumnAbsolute,
[OptionalAttribute] XlReferenceStyle ReferenceStyle,
[OptionalAttribute] Object External,
[OptionalAttribute] Object RelativeTo
)
which the first two apparently are true / false flags, the next is defined as a Microsoft.Office.Interop.Excel.XlReferenceStyle object, and I assume that External is a link to an external file or a flag of some type. RelativeTo, I can only guess that this refers to an arbitrary position, determined, possibly, to a range object, maybe a string. Unfortunately, MSDN is extremely rare in this thread, so I just guess here and post my guesses. However, using this code that I posted, I can get the total used as “A1: B245”, which gives me exactly what I want, and then I can create a new range by extracting the second part and then continue.