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.
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];
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;
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;
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
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.
UsedRange work fine with "virgins" cells, but if your cells are filled in the past, then UsedRange will deliver to you the old value.
"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;
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;
If you are getting an error stating that "Object does not contain a definition for get_range."
Excel.Worksheet sheet = workbook.ActiveSheet; Excel.Range rng = (Excel.Range) sheet.Range[sheet.Cells[1, 1], sheet.Cells[3,3]].Cells;
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;