Excel

Find First or Last Populated Row in a sheet

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

MWE

Description:

xlFirstRow finds the first row in a sheet with ?something? in any cell of the row. xlLastRow finds the last row in a sheet with ?something? in any cell of the row. 

Discussion:

Many procedures or macros in Excel need to know where useful data starts and ends on the target worksheet. Hardcoding first and last rows works, but makes the macro less useful. Integrating the search process for first useful and last useful rows is relatively easy to do; but why repeat the coding for each macro when the method is probably find ?non-blank? rows each time and a simple function, once written, can be used again and again. The function xlFirstRow returns the target sheet row number for the first row that is ?not blank?. The function xlLastRow returns the target sheet row number for the last row that is ?not blank?. In both cases, if any cell in a row is ?non-blank?, then the whole row is considered ?non-blank?. Both procedures have a single, optional argument, the target worksheet. If the target worksheet name is supplied, the row info returned is for the target worksheet. If the worksheet name is not supplied, both procedures default to the active worksheet. The example demonstrates both cases. 

Code:

instructions for use 

			
Option Explicit 
 
Sub Test_xlFirstLastRows() 
     
     '       Target Application:  MS Excel
     '       Demonstration:  display first and last non-blank rows in the active sheet
     '                       and one target sheet
     
    Dim SheetName As String 
     '
     '           display sheet name and results from xlFirstRow and xlLastRow
     '           for the active sheet.  Since activesheet is assumed if procs are called
     '           without a passed arguement, use that method here
     '
    MsgBox "Worksheet name = " & ActiveSheet.Name & vbCrLf & _ 
    "First non-blank row = " & xlFirstRow & vbCrLf & _ 
    "Last non-blank row = " & xlLastRow, vbInformation, _ 
    "Active Sheet Demonstration" 
     '
     '           display sheet name and results from xlFirstRow and xlLastRow
     '           for "Sheet4".  Since this is not the active sheet, the sheet must
     '           be defined via the passed arguement.
     '
    SheetName = "Sheet4" 
    MsgBox "Worksheet name = " & SheetName & vbCrLf & _ 
    "First non-blank row = " & xlFirstRow(SheetName) & vbCrLf & _ 
    "Last non-blank row = " & xlLastRow(SheetName), vbInformation, _ 
    "Passed Sheet Name Demonstration" 
     
     
End Sub 
 
Function xlFirstRow(Optional WorksheetName As String) As Long 
     
     '    find the first populated row in a worksheet
     
    If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name 
    With Worksheets(WorksheetName) 
        On Error Resume Next 
        xlFirstRow = .Cells.Find("*", .Cells(.Cells.Count), xlFormulas, _ 
        xlWhole, xlByRows, xlNext).Row 
        If Err <> 0 Then xlFirstRow = 0 
    End With 
     
End Function 
 
Function xlLastRow(Optional WorksheetName As String) As Long 
     
     '    find the last populated row in a worksheet
     
    If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name 
    With Worksheets(WorksheetName) 
        On Error Resume Next 
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _ 
        xlWhole, xlByRows, xlPrevious).Row 
        If Err <> 0 Then xlLastRow = 0 
    End With 
     
End Function

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. In the left side window, select the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
  5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
  6. Paste the code into the right-hand code window.
  7. Close the VBE, save the file if desired.
  8. See ?Test The Code? below
 

Test the code:

  1. In the attached example, there are 5 sheets with varying amounts of data.
  2. Select any of these sheets (or create another sheet)
  3. Go to Tools | Macro | Macros (or Alt+F8) and double-click on Test_xlFirstLastRows
  4. The results for the active sheet and Sheet4 will be displayed.
  5. Each sheet has a text box with info on what you should expect from the demo. You may also click on any of these text boxes to execute the demo.
  6. (N.B. if you select Sheet4 as the active sheet the same result will be displayed twice.
  7. xlFirstRow and xlLastRow are functions to be called by a higher level procedure, further testing will depend on how the functions are used).
 

Sample File:

FirstLastRows.zip 16.89KB 

Approved by mdmackillop


This entry has been viewed 461 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express