Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to find the last row of Excel 2007

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 01-06-2009
Member
 
Join Date: Nov 2008
Posts: 1,185
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?

Reply With Quote
  #2  
Old 01-06-2009
Member
 
Join Date: May 2008
Posts: 2,290
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.
Reply With Quote
  #3  
Old 01-06-2009
XSI XSI is offline
Member
 
Join Date: May 2008
Posts: 271
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.
Reply With Quote
  #4  
Old 01-06-2009
Member
 
Join Date: May 2008
Posts: 685
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
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to find the last row of Excel 2007"
Thread Thread Starter Forum Replies Last Post
integrating find tool in a cell referring another workbook in MS-excel 2007 shyra_grace Windows Software 1 10-04-2012 11:12 PM
Find / Replace in Excel 2007 KALANI84 Windows Software 4 21-03-2012 01:32 AM
Excel 2007 file fails to get permission to open in Excel 2011 Raju Chacha Windows Software 6 13-01-2012 08:17 PM
How to check data validation compatibility of excel 2010 on excel 2007 Zoello Windows Software 6 17-05-2011 10:00 PM
Cannot find macro working in excel 2007 Visala Windows Software 3 06-08-2009 11:47 PM


All times are GMT +5.5. The time now is 03:55 PM.