Introduction | Example | Tutorial | Applications

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

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

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.

Let me know how you plan on using the Range.End property in the comments below. When you’re ready to take your VBA to the next level, subscribe using the form below.