In VBA when we have to find the last row there are many different methods and the most commonly used method is the End(XLDown) method and there are other methods such as to find 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 the first progress you made in VBA then making the code dynamic is the next step for you. Excel is full of cell references, 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

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

Now I will add more values to the list.

Now if I run the code, it will not give me the updates result rather it still sticks to the old range i.e. Range (“B2: B7”).

This is where the dynamic code is very important.

In the process of making the code dynamic, finding the last used row in the column is very important. In this article, we will discuss the ways of finding the last row in Excel VBA.

### How to Find Last Used Row in the Column?

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

#### Method #1

Before I explain you the code, I 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 use the same method in VBA too 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

**Step 2: **For this variable, we will assign the last used row number.

**Code:**

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

**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

**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

**CELLS(Rows.Count, 1)** means to count how many rows are there in the first column.

4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion

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 shortcut key as **Ctrl + Up Arrow **key.

In VBA we need to use the end key and up i.e. End VBA xlUp

**Code:**

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

**Step 6: **Now 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

**Step 7: **Now the variable holds the last used row number. Show the value of this variable in the message box in VBA code.

**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

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

**Output:**

The last used row in this worksheet is 13.

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

Ok, now the result automatically takes the last row.

This is what the dynamic VBA last row code is.

As I 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

I 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 by using the Range object and special VBA cells property as well.

**Code:**

Sub Last_Row_Example3() Dim LR As Long LR = Range("A:A").SpecialCells(xlCellTypeLastCell).Row MsgBox LR End Sub

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

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

**Output:**

Now I will delete the 12th row and see the result.

Even though I have deleted one row it is still showing the result as 12.

In order to make this code work we need to hit the save button after every action, then this code will return accurate results.

I have saved the workbook and now see the result.

#### Method #3

We can find the VBA last row in the used range. 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

This will also return the last used row.

**Output:**

### Recommended Articles

This has been a Guide to VBA Last Row. Here we learn the top 3 methods to find Last Used Row in a given Column along with examples & downloadable templates. Below are some useful articles related to VBA –

- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion