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

Question

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.

I would start clicking the check mark on the answers that helped you. People are less likely to help if your percentage is low. — Gabriel McAdams, Dec 20 '11 at 02:59
@Wartickler If you look at his profile page, you'll see that he was last seen on SO in 2010. I consider it somewhat unlikely that he'll accept any answers in the foreseeable future :-( — Frank Schmitt, Jun 06 '14 at 07:50
+Dan Crowther profile also shows him as 'unregistered' .. guess his out — Leo Gurdian, Mar 23 '17 at 18:49

burnside · Answer 1 · 2010-02-25T10:50:19.210

96

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];

edited Feb 25 '10 at 10:50

answered Feb 25 '10 at 10:28

burnside

2,551
23
20

Me, too, although I needed to do the same thing (one-cell case) in VBA:`Dim rng As Range` and `Set rng = ActiveSheet.Cells(1, 1)` – Hephaestus Sep 22 '12 at 03:38
The `workbook.ActiveSheet;` could be `oExcel.ThisWorkbook.ActiveSheet;` in which `oExcel` is a Excel instance, right? – Daniel Bonetti Apr 20 '16 at 12:31
1

Which in the `[1, 1]` and `[3, 3]` is the row and which the column? – mirabilos Sep 16 '16 at 14:22
2

@mirabilos `dynamic Range[[object RowIndex], [object ColumnIndex]]` – Alex P. Mar 14 '17 at 09:55
3

`Excel.Worksheet` does not have `get_Range` method (I'm using C# in VS 2010) – Egor Skriptunoff Oct 24 '17 at 15:08
1

@EgorSkriptunoff In VB.Net you can just use Range on Excel.Worksheet, it may be the same in C# – garthhh Oct 18 '18 at 23:24
@garthhh - Yes, tic's answer works for me. But not this answer. – Egor Skriptunoff Oct 19 '18 at 07:07

score 24 · Answer 2 · answered Aug 04 '14 at 22:15

24

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;

answered Aug 04 '14 at 22:15

tic

2,484
1
21
33

1

I'm using version 15.0 and it also doesn't have get_range, thanks tic, this code works great for me – Kyle B Feb 26 '16 at 14:27

score 7 · Answer 3 · answered Nov 15 '18 at 18:56

7

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;

answered Nov 15 '18 at 18:56

user1141820

99
3
9

score 3 · Answer 4 · edited Apr 19 '13 at 09:01

3

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

edited Apr 19 '13 at 09:01

kleopatra

51,061
28
99
211

answered Apr 19 '13 at 08:50

flaming

39
1

score 1 · Answer 5 · answered Feb 19 '17 at 22:01

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

score 0 · Answer 6 · answered Mar 05 '13 at 20:51

0

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.

answered Mar 05 '13 at 20:51

Askjerry

49
4

score 0 · Answer 7 · answered Jul 02 '15 at 14:30

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:

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

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

score 0 · Answer 8 · edited May 11 '17 at 07:37

0

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;

edited May 11 '17 at 07:37

sinsedrix

4,336
4
29
53

answered Jul 01 '16 at 07:39

FaceTowel

19
6

Thanks sinsedrix! – FaceTowel May 14 '17 at 09:17
And Column is just like this, right? Then, if you use UsedRange foreach in addition, row by row, column by column check, you can get like 'new UsedRange' not knowing what the row/column is the last row/column. – FaceTowel May 14 '17 at 09:27

score -1 · Answer 9 · edited Nov 20 '18 at 22:04

-1

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;

edited Nov 20 '18 at 22:04

Daniel B

3,109
2
33
42

answered Nov 20 '18 at 20:59

Ilie

1

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

9 Answers9

Linked