VBA Last Row

Updated on April 23, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

In VBA, when we have to find the last row, there are many different methods. The most commonly used method is the End(XLDown) method. Other methods include finding the last value using the find function in VBA, End(XLDown). The row is the easiest way to get to the last row.

Excel VBA Last Row

If writing the code is your first progress in VBA, then making the code dynamic is your next step. Excel is full of cell referencesExcel Is Full Of Cell ReferencesCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more. The moment we refer to the cell, it becomes fixed. If our data increases, we need to go back to the cell reference and change the references to make the result up to date.

For an example, look at the below code.

Code:

Sub Last_Row_Example1()

   Range("D2").Value = WorksheetFunction.Sum(Range("B2:B7"))

End Sub
VBA Last Row Sum 1

The above code says in D2 cell value should be the summation of Range (“B2:B7”).

VBA Last Row Sum 1-1

Now, we will add more values to the list.

VBA Last Row Sum 1-2

Now, if we run the code, it will not give us the updated result. Rather, it still sticks to the old range, i.e., Range (“B2: B7”).

That is where dynamic code is very important.

Finding the last used row in the column is crucial in making the code dynamic. This article will discuss finding the last row in Excel VBA.

How to Find the Last Used Row in the Column?

Below are the examples to find the last used row in Excel VBA.

You can download this VBA Last Row Template here – VBA Last Row Template

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Method #1

Before we explain the code, we want you to remember how you go to the last row in the normal worksheet.

We will use the shortcut key Ctrl + down arrow.

It will take us to the last used row before any empty cell. We will also use the same VBA method to find the last row.

Step 1: Define the variable as LONG.

Code:

Sub Last_Row_Example2()
    
   Dim LR As Long
   'For understanding LR = Last Row
End Sub
VBA Last Row Method 1

Step 2: We will assign this variable’s last used row number.

Code:

Sub Last_Row_Example2()

   Dim LR As Long
   'For understanding LR = Last Row

   LR =

End Sub
Step 2 - We will assign this variable's last used row number

Step 3: Write the code as CELLS (Rows.Count,

Code:

Sub Last_Row_Example2()

   Dim LR As Long
   'For understanding LR = Last Row

   LR = Cells(Rows.Count,

End Sub
VBA Last Row Method 1-2

Step 4: Now, mention the column number as 1.

Code:

Sub Last_Row_Example2()

   Dim LR As Long
   'For understanding LR = Last Row
   LR = Cells(Rows.Count, 1)

End Sub
VBA Last Row Method 1-3

CELLS(Rows.Count, 1) means counting how many rows are in the first column.

So, the above VBA code will take us to the last row of the Excel sheet.

Step 5: If we are in the last cell of the sheet to go to the last used row, we will press the Ctrl + Up Arrow keys.

In VBA, we need to use the end key and up, i.e., End VBA xlUpVBA XlUpVBA XLUP is a range and property method for locating the last row of a data set in an excel table. This snippet works similarly to the ctrl + down arrow shortcut commonly used in Excel worksheets.read more

Code:

Sub Last_Row_Example2()

   Dim LR As Long
   'For understanding LR = Last Row

   LR = Cells(Rows.Count, 1).End(xlUp)

End Sub
VBA Last Row Method 1-4

Step 6: It will take us to the last used row from the bottom. Now, we need the row number of this. So, use the property ROW to get the row number.

Code:

Sub Last_Row_Example2()

  Dim LR As Long
  'For understanding LR = Last Row

  LR = Cells(Rows.Count, 1).End(xlUp).Row

End Sub
VBA Last Row Method 1-5

Step 7: Now, the variable holds the last used row number. Show the value of this variable in the message box in the VBA codeMessage Box In VBA CodeVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

Code:

Sub Last_Row_Example2()

  Dim LR As Long
  'For understanding LR = Last Row

  LR = Cells(Rows.Count, 1).End(xlUp).Row

  MsgBox LR

End Sub
VBA Last Row Method 1-6

Run this code using the F5 key or manually. It will display the last used row.

VBA Last Row Method 1-7

Output:

VBA Last Row Method 1-8

The last used row in this worksheet is 13.

Now, we will delete one more line, run the code, and see the dynamism of the code.

VBA Last Row Method 1-9

Now, the result automatically takes the last row.

That is what the dynamic VBA last row code is.

As we showed in the earlier example, change the row number from a numeric value to LR.

Code:

Sub Last_Row_Example2()

   Dim LR As Long
   'For understanding LR = Last Row

    LR = Cells(Rows.Count, 1).End(xlUp).Row

    Range("D2").Value = WorksheetFunction.Sum(Range("B2:B" & LR))

End Sub
VBA Last Row Method 1-10

We have removed B13 and added the variable name LR.

Now, it does not matter how many rows you add. It will automatically take the updated reference.

Method #2

We can also find the last row in VBA using the Range objectVBA Using The Range ObjectRange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more and special VBA cells propertyVBA Cells PropertyCells are cells of the worksheet, and in VBA, when we refer to cells as a range property, we refer to the same cells. In VBA concepts, cells are also the same, no different from normal excel cells.read more.

Code:

Sub Last_Row_Example3()

   Dim LR As Long

   LR = Range("A:A").SpecialCells(xlCellTypeLastCell).Row

   MsgBox LR
End Sub
Method 2

The code also gives you the last used row. For example, look at the below worksheet image.

Method 2-1

If we run the code manually or use the F5 key result will be 12 because 12 is the last used row.

VBA Last Row Method 2-3

Output:

Method 2-2

Now, we will delete the 12th row and see the result.

Method 2-4

Even though we have deleted one row, it still shows the result as 12.

To make this code work, we must hit the “Save” button after every action. Then this code will return accurate results.

We have saved the workbook and now see the result.

Method 2-5

Method #3

We can find the VBA last row in the used range. For example, the below code also returns the last used row.

Code:

Sub Last_Row_Example4()

   Dim LR As Long

   LR = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

   MsgBox LR
End Sub
Method 3

It will also return the last used row.

VBA Last Row Method 3-1

Output:

Method 3-2

This article has been a guide to VBA Last Row. Here, we learn the top 3 methods to find the last used row in a given column, along with examples and downloadable templates. Below are some useful articles related to VBA: –