Try the following, one function gets last used row for a column, one for last used row for a sheet.
Example
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim fileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx")
Dim sheetName As String = "Sheet1"
Dim lastRow As Integer = 0
lastRow = UseRowsdByColumn(fileName, sheetName, "A")
MessageBox.Show(lastRow.ToString)
End Sub
End Class
Code Module
Option Strict On
Option Infer Off
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Module ExcelCode
''' <summary>
''' Get last used row in sheetname
''' </summary>
''' <param name="FileName">path and filename to excel file to work with</param>
''' <param name="SheetName">Worksheet name to get information</param>
''' <returns>-1 if issues else lasted used row</returns>
''' <remarks></remarks>
Public Function UsedRows(ByVal FileName As String, ByVal SheetName As String) As Integer
Dim RowsUsed As Integer = -1
If IO.File.Exists(FileName) Then
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Dim xlWorkSheets As Excel.Sheets = Nothing
xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlWorkBooks = xlApp.Workbooks
xlWorkBook = xlWorkBooks.Open(FileName)
xlApp.Visible = False
xlWorkSheets = xlWorkBook.Sheets
For x As Integer = 1 To xlWorkSheets.Count
xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
If xlWorkSheet.Name = SheetName Then
Dim xlCells As Excel.Range = Nothing
xlCells = xlWorkSheet.Cells
Dim thisRange As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
RowsUsed = thisRange.Row
Marshal.FinalReleaseComObject(thisRange)
thisRange = Nothing
Marshal.FinalReleaseComObject(xlCells)
xlCells = Nothing
Exit For
End If
Marshal.FinalReleaseComObject(xlWorkSheet)
xlWorkSheet = Nothing
Next
xlWorkBook.Close()
xlApp.UserControl = True
xlApp.Quit()
ReleaseComObject(xlWorkSheets)
ReleaseComObject(xlWorkSheet)
ReleaseComObject(xlWorkBook)
ReleaseComObject(xlWorkBooks)
ReleaseComObject(xlApp)
Else
Throw New Exception("'" & FileName & "' not found.")
End If
Return RowsUsed
End Function
''' <summary>
''' Get last used row for a single column
''' </summary>
''' <param name="FileName"></param>
''' <param name="SheetName"></param>
''' <param name="Column"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function UseRowsdByColumn(ByVal FileName As String, ByVal SheetName As String, ByVal Column As String) As Integer
Dim LastRowCount As Integer = 1
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Dim xlWorkSheets As Excel.Sheets = Nothing
xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlWorkBooks = xlApp.Workbooks
xlWorkBook = xlWorkBooks.Open(FileName)
xlApp.Visible = False
xlWorkSheets = xlWorkBook.Sheets
For x As Integer = 1 To xlWorkSheets.Count
xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
If xlWorkSheet.Name = SheetName Then
Dim xlCells As Excel.Range = xlWorkSheet.Cells()
Dim xlTempRange1 As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
Dim xlTempRange2 As Excel.Range = xlWorkSheet.Rows
Dim xlTempRange3 As Excel.Range = xlWorkSheet.Range(Column.ToUpper & xlTempRange2.Count)
Dim xlTempRange4 As Excel.Range = xlTempRange3.End(Excel.XlDirection.xlUp)
LastRowCount = xlTempRange4.Row
Marshal.FinalReleaseComObject(xlTempRange4)
xlTempRange4 = Nothing
Marshal.FinalReleaseComObject(xlTempRange3)
xlTempRange3 = Nothing
Marshal.FinalReleaseComObject(xlTempRange2)
xlTempRange2 = Nothing
Marshal.FinalReleaseComObject(xlTempRange1)
xlTempRange1 = Nothing
Marshal.FinalReleaseComObject(xlCells)
xlCells = Nothing
End If
Marshal.FinalReleaseComObject(xlWorkSheet)
xlWorkSheet = Nothing
Next
xlWorkBook.Close()
xlApp.UserControl = True
xlApp.Quit()
ReleaseComObject(xlWorkSheets)
ReleaseComObject(xlWorkSheet)
ReleaseComObject(xlWorkBook)
ReleaseComObject(xlWorkBooks)
ReleaseComObject(xlApp)
Return LastRowCount
End Function
Public Sub ReleaseComObject(ByVal obj As Object)
Try
If obj IsNot Nothing Then
Marshal.ReleaseComObject(obj)
obj = Nothing
End If
Catch ex As Exception
obj = Nothing
End Try
End Sub
End Module
