[c#] How do I get an Excel range using row and column numbers in VSTO / C#?

I think the question sums it up. Given two integers for row and column or four integers for row and column for the two corners of a range, how do I get a range object for that range.

UsedRange work fine with "virgins" cells, but if your cells are filled in the past, then UsedRange will deliver to you the old value.

For example:

"Think in a Excel sheet that have cells A1 to A5 filled with text". In this scenario, UsedRange must be implemented as:

Long SheetRows;
SheetRows = ActiveSheet.UsedRange.Rows.Count;

A watch to SheetRows variable must display a value of 5 after the execution of this couple of lines.

Q1: But, what happen if the value of A5 is deleted?

A1: The value of SheetRows would be 5

Q2: Why this?

A2: Because MSDN define UsedRange property as:

Gets a Microsoft.Office.Interop.Excel.Range object that represents all the cells that have contained a value at any time.

So, the question is: Exist some/any workaround for this behavior?

I think in 2 alternatives:

1. Avoid deleting the content of the cell, preferring deletion of the whole row (right click in the row number, then "delete row".
2. Use CurrentRegion instead of UsedRange property as follow:

Long SheetRows;
SheetRows = ActiveSheet.Range("A1").CurrentRegion.Rows.Count;

If you want like Cells(Rows.Count, 1).End(xlUp).Row , you can do it.

just use the following code:

using Excel = Microsoft.Office.Interop.Excel;

string xlBk = @"D:\Test.xlsx";
Excel.Application xlApp;
Excel.Workbook xlWb;
Excel.Worksheet xlWs;

Excel.Range rng;
int iLast;

xlApp = new Excel.Application();
xlWb = xlApp.Workbooks.Open(xlBk, 0, true, 5, "", "", true, 
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

xlWs = (Excel.Worksheet)xlWb.Worksheets.get_Item(1);

iLast = xlWs.Rows.Count;
rng = (Excel.Range)xlWs.Cells[iLast, 1];
iLast = rng.get_End(Excel.XlDirection.xlUp).Row;

Try this, works!

Excel.Worksheet sheet = xlWorkSheet;
Excel.Series series1 = seriesCollection.NewSeries();
Excel.Range rng = (Excel.Range)xlWorkSheet.Range[xlWorkSheet.Cells[3, 13], xlWorkSheet.Cells[pp, 13]].Cells;
series1.Values = rng;

you can retrieve value like this

string str = (string)(range.Cells[row, col] as Excel.Range).Value2 ;

select entire used range

Excel.Range range = xlWorkSheet.UsedRange;

source :

http://csharp.net-informations.com/excel/csharp-read-excel.htm

flaming

If you are getting an error stating that "Object does not contain a definition for get_range."

Try following.

Excel.Worksheet sheet = workbook.ActiveSheet;
Excel.Range rng = (Excel.Range) sheet.Range[sheet.Cells[1, 1], sheet.Cells[3,3]].Cells;

I found a good short method that seems to work well...

Dim x, y As Integer
x = 3: y = 5  
ActiveSheet.Cells(y, x).Select
ActiveCell.Value = "Tada"

In this example we are selecting 3 columns over and 5 rows down, then putting "Tada" in the cell.

The given answer will throw an error if used in Microsoft Excel 14.0 Object Library. Object does not contain a definition for get_range. Instead use

int countRows = xlWorkSheetData.UsedRange.Rows.Count;
int countColumns = xlWorkSheetData.UsedRange.Columns.Count;
object[,] data = xlWorkSheetData.Range[xlWorkSheetData.Cells[1, 1], xlWorkSheetData.Cells[countRows, countColumns]].Cells.Value2;

Facing the same problem I found the quickest solution was to actually scan the rows of the cells I wished to sort, determine the last row with a non-blank element and then select and sort on that grouping.

    Dim lastrow As Integer
lastrow = 0
For r = 3 To 120
   If Cells(r, 2) = "" Then
        Dim rng As Range
        Set rng = Range(Cells(3, 2), Cells(r - 1, 2 + 6))
        rng.Select
        rng.Sort Key1:=Range("h3"), order1:=xlDescending, Header:=xlGuess, DataOption1:=xlSortNormal
        r = 205
    End If
Next r

Where the range is multiple cells:

Excel.Worksheet sheet = workbook.ActiveSheet;
Excel.Range rng = (Excel.Range) sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[3,3]);

Where range is one cell:

Excel.Worksheet sheet = workbook.ActiveSheet;
Excel.Range rng = (Excel.Range) sheet.Cells[1, 1];