How To Find The Last Row In Excel Vba

by

How to Find the Last Row in Excel VBA

In Excel VBA, you can use the “End” property along with the “xlUp” constant to find the last row in a specific column. This method is useful when you want to determine the range of data in a worksheet programmatically. Here’s an example to help you understand how it works: 

    
      Sub FindLastRowExample()
          Dim lastRow As Long
          lastRow = Cells(Rows.Count, 1).End(xlUp).Row
          MsgBox "The last row in column A is: " & lastRow
      End Sub

In the above example, we are using the “Cells” method with the “Rows.Count” property to reference the last cell in the worksheet. This ensures that we cover the entire column, regardless of the number of rows. We then use the “End” property with the “xlUp” constant to find the last filled cell in the specified column.

Once we have the reference to the last cell, we use the “Row” property to get the row number and store it in the “lastRow” variable. Finally, we display a message box with the last row number.

You can modify the above code to find the last row in a different column by changing the column index in the “Cells” method.

Here’s another example that demonstrates finding the last row in a specific column and then selecting the entire range from the first row to the last row: 

    
      Sub SelectRangeExample()
          Dim lastRow As Long
          Dim rng As Range
          lastRow = Cells(Rows.Count, 2).End(xlUp).Row
          Set rng = Range(Cells(1, 2), Cells(lastRow, 2))
          rng.Select
      End Sub

In this example, we declare a range variable named “rng” to store the range from the first row to the last row in column B. We then use the “Set” statement to assign the range to the variable. Finally, we use the “Select” method on the range to visually select the range in the worksheet.

You can adapt the above code to perform various operations with the last row, such as copying data, applying formatting, or performing calculations.

I hope this explanation helps you understand how to find the last row in Excel VBA using the “End” property. Have fun experimenting with it!

Leave a comment