Results 1 to 4 of 4

Thread: How to find the last row of Excel 2007

  1. #1
    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?

  2. #2
    Join Date
    May 2008
    Posts
    2,302

    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.

  3. #3
    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.

  4. #4
    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

Similar Threads

  1. Replies: 1
    Last Post: 10-04-2012, 11:12 PM
  2. Find / Replace in Excel 2007
    By KALANI84 in forum Windows Software
    Replies: 4
    Last Post: 21-03-2012, 01:32 AM
  3. Excel 2007 file fails to get permission to open in Excel 2011
    By Raju Chacha in forum Windows Software
    Replies: 6
    Last Post: 13-01-2012, 08:17 PM
  4. Replies: 6
    Last Post: 17-05-2011, 10:00 PM
  5. Cannot find macro working in excel 2007
    By Visala in forum Windows Software
    Replies: 3
    Last Post: 06-08-2009, 11:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •