In the following section you can see How to find the last row data in Excel worksheet or How to find the Used area in a worksheet. For finding last row we are using WorkSheet.UsedRange . It will return the last cell . The follwoing code open an Excel file and read the entire content.
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim range As Excel.Range
Dim rCnt As Integer
Dim cCnt As Integer
Dim Obj As Object
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open("c:\vbexcel.xlsx")
xlWorkSheet = xlWorkBook.Worksheets("sheet1")
range = xlWorkSheet.UsedRange
For rCnt = 1 To range.Rows.Count
For cCnt = 1 To range.Columns.Count
Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
MsgBox(Obj.value)
Next
Next
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
End Class