Shamima Sultana
In Excel VBA, you can use different methods to find the last row. In this article, I am going to explain different methods of to find last row in Excel VBA. To make this explanation more understandable, seethe below overview. There are 4 columns in the dataset representing sales information. These columns are Sales Person, Region, Product, and Price.
Table of Contents
hide
Download Practice Workbook
5 Suitable Ways to Find Last Row Using Excel VBA
In this article, we will use VBA SpecialCells, Row.Count, Used.Range, Range.Find methods to get the last row in our dataset.
1. Using SpecialCells to Find Last Row
You can use the SpecialCells method to find the last row using VBA.
- Initially, open the Developer tab >> then select Visual Basic.
- A new window of Microsoft Visual Basic for Applications will pop up. Now, from Insert >> select Module option.
- Subsequently, 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
- 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 option.
- Then, a dialog box will pop up.
- 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.
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.
- Firstly, open a module box like method 1.
- After creating the box, 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
- 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.
- Like the previous method, Save the code and go back to the worksheet. Here, again open the View tab >> from Macros >> select View Macros like the previous method.
- 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.
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.
- To begin with, 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
- 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 column B is 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, in the Macro dialog box, 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.
Thus, it will pop up a message box showing the last row number.
Similar Readings:
- VBA Find in Column in Excel (7 Approaches)
- Find and Replace Using VBA (11 Ways)
- Find Exact Match Using VBA in Excel (5 Ways)
- How to Find String with VBA in Excel (8 Examples)
4. Using UsedRange to Find Last Row
You can use the UsedRange property of the worksheet to find the last row using VBA.
- Create a Module box and write the following code in the Module as shown in example 1.
Sub LastRow_UsedRange()
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
MsgBox "Last Row: " & LastRow
End Sub
- Here, I’ve created a sub-procedure called LastRow_UsedRange, where a Long type of variable LastRow has been declared.
- Next, define 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. - Further, in the Macro dialog box, 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.
Thus, it will pop up a message box showing the last row number.
5. Find Last Row Using Range.Find Method
You can use the Range.Find method to find the last row using VBA.
- First, 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
- 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. SearchOrder:=xlByRows parameter will move right-to-left and also loops up through each row until it finds a non-empty cell.
- Now, in the Macro dialog box, 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.
Conclusion
In this article, I’ve explained 5 ways to VBA find the last row in Excel. You can find similar problem solving methods like this in our Exceldemy website. In case you have any confusion or question regarding these methods you may comment down below.
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.
For further queries, you can send me your Excel file.
Thanks
Shamima Sultana
Your guidance is wonderful Shamima. Thank you so much. Do the methods you presented work the same way for Excel Tables?
Greetings, Michael. I’m responding on ExcelDemy’s behalf. Yes, the methods presented work the same way for Excel tables after I converted the provided dataset into a table.