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?
Re: How to find the last row of Excel 2007
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.
Re: How to find the last row of Excel 2007
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.
Re: How to find the last row of Excel 2007
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