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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to c#

Examples related to excel

Examples related to vsto