Quick Jump
Introduction | Example | Tutorial | Applications | Comments

Introduction - Last Row End(xlUp).Row

The VBA snippet End(xlup).Row will find the last used row in an Excel range. Knowing the last row in Excel is useful for looping through columns of data.

My quest to discover this versatile VBA snippet began years ago with the need (or desire?) to rapidly march through columned data. Such is the life of a nuclear engineer. It’s safe to say, I now use this VBA code more than any other in my Excel macros. It wouldn’t be an overstatement to claim that 9 out of 10 of my products require I identify the last used cell.

Example - Last Row End(xlUp).Row

Concatenate Number to each Item in Range

Option Explicit
Sub FindLastRow()
 Dim iLastRow As Integer
 Dim i As Integer
 iLastRow = ActiveSheet.Range("a10000").End(xlUp).Row
 
 For i = 1 To iLastRow
    ActiveSheet.Range("a" & i) = i & ") " & ActiveSheet.Range("a" & i)
 Next i
End Sub

Tutorial - Last Row End(xlUp).Row

This short subroutine loops through each row and adds a number to the beginning of all entries. The versatility of the code is what makes it so powerful. You no longer need to know how many items you have in your list.

Before we begin the tutorial, let’s see the subroutine in action.

VBA Find Last Row Before
Before

VBA Find Last Row Before
After

The line iLastRow = ActiveSheet.Range("a10000").End(xlUp).Row inherently makes four assumptions:

  1. Your list has less than 10000 entries. If there’s a potential your list will contain more, simply increase the integer portion of the range object.
  2. Your data are stored in column A. Change the column designator if your data are stored elsewhere. (Yes, “data are” is correct. The word “data” is plural.)
  3. Your list is located on the sheet you currently have open and active. If your list will always been on a sheet named data, you can change ActiveSheet to Sheets("data")
  4. Your list is located in your active workbook. If your list is stored in an open Excel workbook named mydata.xls, add Workbooks("mydata.xls"). immediately before your Sheet property.

For values stored in column “C”, the modified code could look like Workbooks("mydata.xls").Sheets("data").Range("c50000").End(xlUp).Row

Application Ideas - Last Row End(xlUp).Row

My Operations Support Program is just one example of how I used End(xlup).Row to automate my nuclear engineering work.

Here are a few other suggestions:

  1. Compare two columns for differences
  2. Send email to each person in a list
  3. Search each row until you find a matching string
  4. Use offset command to retrieve adjacent data
  5. Write bash scripts for each file in list
  6. Advanced conditional formatting for rows meeting custom criteria
  7. Create an interface that reads user input, post processes it and formats it in a way that is compatible with other programs. I do this all time; Nothing beats using VBA to write scripts for other programs.

Comments

Let me know how you plan on using the Range.End property in the comments below. If you’re looking for more creative ways to find the last row, check out the TheSpreadsheetGuru’s 5 Ways to Find The Last Row.


wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.