VBA Find Last Row in Excel (5 ways)

In the VBA you can use different methods to find the last row. In this article, I am going to explain different methods of VBA find last row in Excel.

To make this explanation visible, I’m going to use a sample dataset. There are 4 columns in the dataset representing sales information. These columns are Sales Person, Region, Product, and Price.

Sample Dataset

Download To Practice

Ways to VBA Find Last Row in Excel

1. Using SpecialCells to Find Last Row

You can use the SpecialCells method to find the last row using VBA.

First, open the Developer tab >> then select Visual Basic

Using SpecialCells VBA to Find Last Row

A new window of Microsoft Visual Basic for Applications will pop up.
Now, from Insert >> select Module

A Module will be opened.
Then write the following code in the Module.

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

Using SpecialCells VBA to Find Last Row

Here, I’ve created a sub procedure called LastRow_SpecialCells, where a Long type of variable LastRow has been declared.

Then defined the variable using the Range.SpecialCells method. Here, I’ve used column A (A:A) as the range. Provided xlCellTypeLastCell as the type parameter of SpecialCells, this will return the last cell for the range (for this case, from column A).

I’ve used a message box to show the result.

After that, Save the code and go back to the worksheet.
Again, open the View tab >> from Macros >> select View Macros

Then, a dialog box will pop up.

Using SpecialCells VBA to Find Last Row

Now, from the Macro name select the LastRow_SpecialCells also select the workbook within Macros in.

Finally, Run the selected Macro.
Thus, it will pop up a message box showing the last row number.

2. Using Rows.Count for Non-Empty Cells

You can use the Rows.Count method to find the last row using VBA.

Now, open the Developer tab >> then select Visual Basic

Using Rows.Count for Non-Empty Cells to Find Last Row

A new window of Microsoft Visual Basic for Applications will pop up.
Now, from Insert >> select Module

A Module will be opened.
Then write the following code in the Module.

Sub LastRow_NonEmpty()
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox LastRow
End Sub

Using Rows.Count for Non-Empty Cells to Find Last Row

Here, I’ve created a sub procedure called LastRow_NonEmpty, where a Long type of variable LastRow has been declared.

Now, CELLS(Rows.Count, 1) will count how many rows are there in the first column. Then used End(xlUp).Row now this will find the last used row in an Excel range.

In the end, I’ve used a message box to show the result.

Then, Save the code and go back to the worksheet.
Here, open the View tab >> from Macros >> select View Macros

Using Rows.Count for Non-Empty Cells to Find Last Row

Now, a dialog box will pop up.

Then, from the Macro name select the LastRow_NonEmpty also select the workbook within Macros in.

Finally, Run the selected Macro.
Thus, it will pop up a message box showing the last row number.

3. Using Rows.Count for Any Selected Column

By using any selected column in VBA, you can find the last row.

First, open the Developer tab >> then select Visual Basic

Using Rows.Count for Any Selected Column to Find Last Row

A new window of Microsoft Visual Basic for Applications will pop up.
Now, from Insert >> select Module

A Module will be opened.
Then write the following code in the Module.

Sub LastRow_AnyColumn()
Dim LastRow As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
MsgBox LastRow
End Sub

Using Rows.Count for Any Selected Column to Find Last Row

Here, I’ve created a sub procedure called LastRow_AnyColumn, where a Long type of variable LastRow has been declared.

Then, in the Range given the column B as a parameter and also  Rows.Count, this will count how many rows are there in a given column B. Next, used End(xlup). Row which will find the last used row in an Excel range.
Last, I’ve used a message box to show the result.

Next, Save the code and go back to the worksheet.
Then, open the View tab >> from Macros >> select View Macros

Using Rows.Count for Any Selected Column to Find Last Row

Here, a dialog box will pop up.

Now, from the Macro name select the LastRow_AnyColumn also select the workbook within Macros in.

Finally, Run the selected Macro.
Thus, it will pop up a message box showing the last row number.

Using Rows.Count for Any Selected Column to Find Last Row


Similar Readings:


4. Using UsedRange to Find Last Row

You can use the UsedRange property of the worksheet to find the last row using VBA.

Now, open the Developer tab >> then select Visual Basic

Using UsedRange to Find Last Row

Then, a new window of Microsoft Visual Basic for Applications will pop up.
Now, from Insert >> select Module

A Module will be opened.
Then, write the following code in the Module.

Sub LastRow_UsedRange()
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
MsgBox "Last Row: " & LastRow
End Sub

Using UsedRange to Find Last Row

Here, I’ve created a sub procedure called LastRow_UsedRange, where a Long type of variable LastRow has been declared.

Next, defined the variable using the ActiveSheet.UsedRange.Rows method also provided ActiveSheet.UsedRange.Rows.Count as the parameter of ActiveSheet.UsedRange.Rows, this will return the last row.
I’ve used a message box to show the result.

Now, Save the code and go back to the worksheet.
Then, open the View tab >> from Macros >> select View Macros

Next, a dialog box will pop up.

Using UsedRange to Find Last RowThen, from the Macro name select the LastRow_UsedRange also select the workbook within Macros in.

Finally, Run the selected Macro.
Thus, it will pop up a message box showing the last row number.

5. Using Range.Find to Find Last Row

You can use the Range.Find method to find the last row using VBA.
Now, open the Developer tab >> then select Visual Basic

Using Range.Find VBA to Find Last RowHere, a new window of Microsoft Visual Basic for Applications will pop up.
Now, from Insert >> select Module

A Module will be opened.
Then write the following code in the Module.

Sub Range_Find_Method()
Dim LastRow As Long
On Error Resume Next
LastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
MsgBox "Last Row: " & LastRow
End Sub

Using Range.Find VBA to Find Last Row

Here, I’ve created a sub procedure called Range_Find_Method, where a Long type of variable LastRow has been declared.

Then defined the variable using the Cells.Find method. Here, declared 7 parameters. In What parameter I used (“*”) which will find the first non-empty cell. Given A1 as the range in the After parameter to start. In the LookAt parameter provided xlPart to look at any part of the text inside the cell.

LookIn:=xlFormulas parameter will look for formulas if there are any. SearchOrder:=xlByRows parameter will move right-to-left and also loops up through each row until it finds a non-empty cell.

MatchCase:=False parameter will tell Find not to consider upper or lower case letters.  When a non-blank is found it stops and returns the row number.

I’ve used a message box to show the result.

Now, Save the code and go back to the worksheet.
Here, open the View tab >> from Macros >> select View Macros

Now, a dialog box will pop up.

Using Range.Find VBA to Find Last Row

Now, from the Macro name select the  Range_Find_Method also select the worksheet within Macros in.

Finally, Run the selected Macro.
Thus, it will pop up a message box showing the last row number.

Using Range.Find VBA to Find Last Row

Conclusion

In this article, I’ve explained 5 ways to VBA find the last row in Excel. You can follow any of the methods to find the last row. In case you have any confusion or question regarding these methods you may comment down below.


Further Readings

Tags:

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

2 Comments
  1. Good afternoon Shamima,
    Thanks so much for your guidance. I’ve written the code to add data to the selected worksheets but instead of adding the data to last row it overwrites it. Where do I go wrong?

    TargetSheet = Cmb_Months.Value
    If TargetSheet = “” Then
    Exit Sub
    End If
    Worksheets(TargetSheet).Activate
    lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    ActiveSheet.Cells(lastrow + 6, 2).Value = Cmb_Area.Value
    ActiveSheet.Cells(lastrow + 6, 3).Value = Txt_Ln_Manager.Value
    ActiveSheet.Cells(lastrow + 6, 4).Value = Txt_FName.Value
    ActiveSheet.Cells(lastrow + 6, 5).Value = Txt_Surname.Value
    ActiveSheet.Cells(lastrow + 6, 6).Value = Txt_S_Number.Value

    • Hi Deon Bailey,
      Hope you are doing well. Thanks for reaching out to me with your issue.

      As you didn’t share your Excel file that’s why it is hard to understand your sheet name and where is the problem occurring.

      But you need not to worry, I’m giving you a possible solution so that you can add data to your selected sheet to the last row.

      The reason for overwriting the added data is your code wasn’t finding the last row it was showing 1 as the last row number.

      Here, I added data according to my dataset I used in this article. I commented out your code and added some required lines.

      Sub Insert_Value_from_LastRow()

      ‘TargetSheet = Cmb_Months.Value
      ‘If TargetSheet = “” Then
      ‘Exit Sub
      ‘End If
      Dim targetSheet As Worksheet
      Set targetSheet = ThisWorkbook.Worksheets(“Dataset”)
      targetSheet.Activate
      ‘lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
      LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
      MsgBox LastRow

      ‘ActiveSheet.Cells(LastRow + 6, 2).Value = Cmb_Area.Value
      ‘ActiveSheet.Cells(LastRow + 6, 3).Value = Txt_Ln_Manager.Value
      ‘ActiveSheet.Cells(LastRow + 6, 4).Value = Txt_FName.Value
      ‘ActiveSheet.Cells(LastRow + 6, 5).Value = Txt_Surname.Value
      ‘ActiveSheet.Cells(LastRow + 6, 6).Value = Txt_S_Number.Value

      ActiveSheet.Cells(LastRow + 3, 2).Value = “Rachel Ross”
      ActiveSheet.Cells(LastRow + 3, 3).Value = “Germany”
      ActiveSheet.Cells(LastRow + 3, 4).Value = “laptop”
      ActiveSheet.Cells(LastRow + 3, 5).Value = 4567

      End Sub

      Note: Whenever you want to insert or add data after a particular row it is better to see the last row number by using MsgBox. It will help you to understand why data is overlapping.

      I also added the images.

      reply of comment

      reply of comment

      For further queries, you can send me your Excel file.

      Thanks
      Shamima Sultana

Leave a reply

ExcelDemy
Logo