I am attempting to use a reference to interop.excel instead of vba to do some worksheet formatting. My code looks like:
Dim lastrow As Range = excel.Rows.End(XlDirection.xlDown)
Dim findme As Range = excel.Range("A1:A" & lastrow)
the excel.range does not allow me to use the "&" symbol here. How should I identify my last row of data?
Looking at the Range Class Reference, it seems you just missed to put the Row property of the lastrow Range:
Dim findme As Range = excel.Range("A1:A" & lastrow.row)
I know this is an old thread but there are a lot of google references with "bad solutions", I think this is the an elegant solution to get the lasCell
set lastCell=yourSheet.Range("A1").SpecialCells(xlCellTypeLastCell)
so your code could be:
Dim lastrow As Range = excel.Range("A1").SpecialCells(xlCellTypeLastCell).Row
Dim findme As Range = excel.Range("A1:A" & lastrow)
lastrow is defined as a Range and you are trying to do a string concatenation between a string and a Range.
Dim endrow As Range = excel.Rows.End(XlDirection.xlDown) Dim lastrow As Integer = endrow.Row Dim cells As Range = excel.Range("A1:A" & lastrow) Now my next question would be, which column does excel.rows.end(xldirection.xldown) search for? Or does it extend to the absolute last row in the entire spreadsheet?It looks at "A". But beware, if there is a merged cell going vertical, it takes the top cell of the merger. So if there is a merged cell going from A15 to A20 which is the last cell of the column, it will then classify A15 as the last cell even though its A20. I find that Neolisk's solution is the best in a case like that.
@Jason Bayldon It ends at last row where there is an entry. It skips blanks also. I am using VB.NET 2010. Here is the sample code.
Dim r2 As Excel.Range
Dim lastrow As Integer
r2 = oSheet.Range("Task_ID") 'Task_ID is my column name
lastrow = r2.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row 'returns last row
