How to find the last row of Excel 2007

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?

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

Code:

---

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.

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.

Here is what you want:

Code:

---

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

---