CODEWITHSUNDEEP
vb.netexcel-interop
CBRF23
CBRF23

Reputation: 1518

How can I get a range object from the row and column index using Excel Interop?

I'm learning VB.Net and trying to transition from VBA. How can I retrieve a range typed object using the row and column index?

In VBA I would use Worksheet.Cells([Row,Column]) and that would return an object of type Range.
However using the Excel Interop the return is typed as Object.
Example:

Dim MyExcel as new Application
Dim MyBook as new Workbook=MyExcel.Workbooks.Open("SomePath\SomeFile.xlsx")
Dim MySheet as new Worksheet=MyBook.Sheets("SomeSheet")
MessageBox.Show(CStr(Sheet2015.Cells(1, 1).Value))

Now, the MSDN for Worksheet.Cells says:

Because the Item property is the default property for the Range object, you can specify the row and column index immediately after the Cells keyword. For more information, see the Item property.

But the link to the Item property from that page takes you to Worksheet.Item, NOT Range.Item - so maybe it should say

"The Item property is the default property for the Worksheet object..."

The return of the Worksheet.Item property is an object of type Object, which explains why I don't get intellisense :(

So is there another method I can use that returns a Range object from a worksheet by providing the index (Row,Column) of the cell, instead of the address?

I know I can use Worksheet.Range and provide the A1 referenced address, but this to me is far more convoluted than just using the row and column index.

I also know I can cast the Worksheet.Cells[R,C] as type Range, but I'd prefer to find a way to return a range. (See my other question here about working with COM objects as base type Object)

Any suggestions?

Upvotes: 2

Views: 4339

Answers (1)

DrDonut
DrDonut

Reputation: 904

You can specify the range as follows:

sheetName.Range(sheetName.Cells(row1, col1), sheetName.Cells(row2, col2))

Then you can use this range for whatever you want.

Yes, I realise this answer is very late, but I just ran into the same problem...

Upvotes: 3

Related Questions