How to find the last row of Excel 2007

[windows_user]

Sponsored Links

I am working on Microsoft Excel sheet of Office 2007. I want to find the last row of the sheet and save the value of the first cell in that row to a variable. Lets say the last row of the sheet is 100 and the first cell on the 100th line is Somnath. So I want a variable "employee_name" on which this value "Somnath" is stored. So do you know how to achieve this? Is this possible? Can you help me?

[Zecho]

To find the last Row on an Excel Worksheet using VBA:

Sub FindLastRow()

Dim LastRow As Long

	If WorksheetFunction.CountA(Cells) > 0 Then

		'Search for any entry, by searching backwards by Rows.

		LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
		MsgBox LastRow

	End If

End Sub

The above code just gives you the last row of the Excel sheet. I hope you will find way to get the first cell of that row.

[XSI]

In fact there are two ways to achieve this. Here it is:

LastRow = Cells.Find ("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious). Row

or

LastRow = ActiveSheet.UsedRange.Rows.Count

The second one is more reliable because the first statement won’t work on filtered Excel Worksheet since the code returns the position of the last row which is not filtered.

[fellah]

Here is what you want:

Private Function FindLastRow() As String

Dim LastRow As Long
Dim FirstCell As Range
If objExcel.WorksheetFunction.CountA(objExcel.Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = objExcel.Cells.Find(What:="*", After:=objExcel.Range("A1"), _
searchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If

Set FirstCell = objExcel.Cells(LastRow, 1)

End Function