VBA Code To Find Last Row Or Last Column (Best Way)
Dynamic VBA Code With Last Row/Column
Finding the Last Row or Last Column within your VBA code is key to ensuring your macro doesn’t break in the future.
Early on when I first began writing VBA macro code, I always needed to go back into the code and modify range references. I had created a bunch of macros to clean up and perform analysis on raw data exported from databases and the data never had the same amount of rows from one data pull to the next.
My coding skills dramatically changed the day I realized my VBA code could be dynamic and automatically determine the size of my raw data once executed. I soon came to realize the goal of coding a macro: to write it once and never touch it again.
Variability is also the greatest challenge for any VBA coder as you have to think of every possible change that could occur in the future. I have found writing VBA code that can automatically resize itself is one of the greatest things missing from most average macro user’s code.
In this article, I have compiled a list of the best methods you can use to accomplish finding the last row or column in your data range.
Prep Your Excel Data!
Keep in mind some of these methods may not give you the desired row or column number if you are not setting your spreadsheet up properly or using a well-formatted block of data.
What I mean by a “well-formatted block of data”, is a worksheet with data that starts in cell A1 and does not have any blank rows or columns in the middle of the data.
The below figure illustrates the difference.
In a data set starting in Row 4, you may need to add or subtract a numerical value depending on the method you use. If you are going to be coding for a data set that has blank rows or columns within it, always be sure to test out your code to make sure it is calculating properly.
Find the Last Cell In Your Spreadsheet With Data
Finding the last cell with a value in it is key to determining the last row or last column. There are a couple of different ways you can locate the last cell on your spreadsheet. Let’s take a look!
1. The Find Function Method (Best Method)
This line of VBA code will search all the cells on your sheet and return the row of the last cell with any sort of value stored in it. Because the Find function is set to search from the very bottom of the spreadsheet and upwards, this code can accommodate blank rows in your data.
Dim LastCell As Range
Set LastCell = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)This method ignores empty cells with formatting still in them, which is ideal if you are truly wanting the find the last cell with data in it, not necessarily the last cell that had any modifications done to it.
2. SpecialCells Method
One of the best manual ways to do this is to utilize the Go To Special dialog box.
The Go To Special dialog box has a variety of actions that can be taken to select certain cells or objections on your spreadsheet. One of those options is to select the Last Cell on the active spreadsheet.
You can get to the Go To Special dialog box by using the keyboard shortcut Ctrl + G which will open the Go To dialog box. From there you can click the Special button and you’ll have arrived at the Go To Special dialog box.
In VBA, the select actions in the Go To Special dialog box are simply called SpecialCells. By calling the SpecialCells function in VBA, you gain the same actions, though they have slightly different names. The particular action you’ll want to call is named xlCellTypeLastCell.
The below VBA code stores the last cell found on the spreadsheet with a value in it to a range variable.
Dim LastCell As Range
Set LastCell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)WARNING! This method could give you unintended results as this finds the last cell with any sort of data or formatting associated with it. This means it can return an empty cell that used to have data in it or simply has any formatting changes (like a yellow cell fill color).
7 Ways To Find The Last Row With VBA
There are actually quite a few ways to determine the last row of a data set in a spreadsheet. We will walk through a number of different ways in this article. I have marked specific methods with a “Best Method” tag as these coding practices are the most bullet-proof ways to determine the last row in your spreadsheet data.
1. The Find Function Method (Best Method)
This line of VBA code will search all the cells on your sheet and return the row of the last cell with any sort of value stored in it. Because the Find function is set to search from the very bottom of the spreadsheet and upwards, this code can accommodate blank rows within your data.
Dim LastRow As Long
LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row2. SpecialCells Method
SpecialCells is a function you can call in VBA that will allow you to pinpoint certain cells or a range of cells based on specific criteria. We can use the xlCellTypeLastCell action to find the last cell in the spreadsheet and call for the cell’s row number.
Dim LastRow As Long
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).RowWARNING! This method could give you unintended results as this finds the last cell with any sort of data or formatting associated with it. This means it can return an empty cell that used to have data in it or simply has any formatting changes (like a yellow cell fill color).
3. Ctrl+Shift+End Method
This line of VBA code mimics the keyboard shortcut Ctrl + Shift + End and returns the numerical value of the last row in the range.
Dim LastRow As Long
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row4. UsedRange Method
The Used Range is something that Excel stores a reference to behind the scenes. It represents the area of the spreadsheet that has values in it. The Used Range can be referenced in VBA using the UsedRange object.
You must be careful with the Used Range though, as Excel does not always update the reference in real time. Sometimes when you delete cell content the Used Range will not readjust itself right away. For this reason, it is wise to force the UsedRange object to restructure itself with your VBA code. The below VBA code example calls this restructure/refresh prior to utilizing UsedRange to pull the last row.
Dim LastRow As Long
ActiveSheet.UsedRange 'Refresh UsedRange
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row5. Table Object Method (Best Method)
If you are using a Table Object to store your data, you can use the Table’s name in the below VBA code to return the numerical value of how many rows are in the table.
Dim LastRow As Long
LastRow = ActiveSheet.ListObjects("Table1").Range.Rows.Count6. Named Range Method
If you are using a Named Range to reference your data’s location, you can use the Range name in the below VBA code to return the numerical value of how many rows are in the Named Range.
Dim LastRow As Long
LastRow = ActiveSheet.Range("MyNamedRange").Rows.Count7. Ctrl+Shift+Down Method
Dim LastRow As Long
LastRow = ActiveSheet.Range("A1").CurrentRegion.Rows.CountExpand Your Range To The Last Row
After you have determined the last row, how do you use it? The vast majority of the time you are going to want to store your entire data range to a Range variable. The following code shows you how to incorporate the last row number you calculated into a Range reference.
Dim DataRange As Range
Set DataRange = Range("A1:M" & LastRow)7 Ways To Find The Last Column With VBA
There are actually quite a few ways to determine the last column of a data set in a spreadsheet. We will walk through a number of different ways in this article. I have marked specific methods with a “Best Method” tag as these coding practices are the most bullet-proof ways to determine the last column in your spreadsheet data.
1. The Find Function Method (Best Method)
This line of VBA code will search all the cells on your sheet and return the column of the last cell with any sort of value stored in it. Because the Find function is set to search from the very far right of the spreadsheet and then leftward, this code can accommodate blank columns within your data.
Dim LastColumn As Long
LastColumn = ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column2. SpecialCells Method
SpecialCells is a function you can call in VBA that will allow you to pinpoint certain cells or a range of cells based on specific criteria. We can use the xlCellTypeLastCell action to find the last cell in the spreadsheet and call for the cell’s column number.
Dim LastColumn As Long
LastColumn = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).ColumnWARNING! This method could give you unintended results as this finds the last cell with any sort of data or formatting associated with it. This means it can return an empty cell that used to have data in it or simply has any formatting changes (like a yellow cell fill color).
3. Ctrl+Shift+End Method
Dim LastColumn As Long
LastColumn = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column4. UsedRange Method
Dim LastColumn As Long
ActiveSheet.UsedRange 'Refresh UsedRange
LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column5. Table Object Method (Best Method)
If you are using a Table Object to store your data, you can use the Table’s name in the below VBA code to return the numerical value of how many columns are in the table.
Dim LastColumn As Long
LastColumn = ActiveSheet.ListObjects("Table1").Range.Columns.Count6. Named Range Method
Dim LastColumn As Long
LastColumn = ActiveSheet.Range("MyNamedRange").Columns.Count7. Ctrl+Shift+Right Method
Dim LastColumn As Long
LastColumn = ActiveSheet.Range("A1").CurrentRegion.Columns.CountHow To Expand Your Range To The Last Column
After you have determined the last column, how do you use it? The vast majority of the time you are going to want to store your entire data range to a Range variable. The following code shows you how to incorporate the last column number you calculated into a Range reference.
Dim DataRange As Range
Set DataRange = Range(Cells(1, 1), Cells(100, LastColumn))VBA Function To Find Last Row or Column
Tim provided the inspiration for a function that can return either the last row or column number through a user-defined function for a given worksheet.
An example of how you could call this function to return the last row on the active worksheet would be written as: x = LastRowColumn(ActiveSheet, “Row”)
Function LastRowColumn(sht As Worksheet, RowColumn As String) As Long
'PURPOSE: Function To Return the Last Row Or Column Number In the Active Spreadsheet
'INPUT: "R" or "C" to determine which direction to search
Select Case LCase(Left(RowColumn, 1)) 'If they put in 'row' or column instead of 'r' or 'c'.
Case "c"
LastRowColumn = sht.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Case "r"
LastRowColumn = sht.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Case Else
LastRowColumn = 1
End Select
End FunctionWhat Can I Do With A LastRow Or LastColumn Variable?
There are many things you can do by calculating the last row or last column of a data set. Examples could be:
- Resizing a Pivot Table range
- Looping through cells in a column
- Deleting only the raw data range
There are many, many more examples of this and I’m sure you can think of a few examples yourself.
Let me know in the comments section below how you use resizing a range in your macro code! Also, if you can think of any other ways to use VBA code to find the last row or last column, post your coding method in the comments section so we can improve the current list. I look forward to reading about your experiences.
I Hope This Excel Tutorial Helped!
Hopefully, I was able to explain how you can use VBA code to find the last row or last column of your range to add dynamic capabilities to your macros. If you have any questions about these techniques or suggestions on how to improve this article, please let me know in the comments section below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.