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.
iLastRow = ActiveSheet.Range("a10000").End(xlUp).Row inherently makes four assumptions:
- Your list has less than
10000entries. If there’s a potential your list will contain more, simply increase the integer portion of the range object.
- 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.)
- 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
- Your list is located in your active workbook. If your list is stored in an open Excel workbook named
Workbooks("mydata.xls").immediately before your Sheet property.
For values stored in column “C”, the modified code could look like
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.
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:
- Compare two columns for differences
- Send email to each person in a list
- Search each row until you find a matching string
- Use offset command to retrieve adjacent data
- Write bash scripts for each file in list
- Advanced conditional formatting for rows meeting custom criteria
- 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. 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.