How to Use VBA to Go to the Last Row in an Excel Sheet

by Jaime Avelar
Find the last row using Visual Basic for Applications (VBA) in Excel.

Find the last row using Visual Basic for Applications (VBA) in Excel.

Learning how to use Visual Basic for Applications (VBA) in Excel can save you time when searching for data in a worksheet. Microsoft Excel, a spreadsheet application included in Microsoft Office, gives you the tools to easily analyze data by using formulas for complex calculations. Use VBA to automate routine tasks in Excel or other Microsoft Office applications.

1

Open Microsoft Excel 2007, click the "Developer" tab and select "Visual Basic."

Type "Sub goToLastRow()" and press "Enter" to create a new sub.

2

Type the following text to create variables:

"Dim lastRow As Integer

Dim X As Integer"

3

Type "For X = 1 To 25

Range("A" & X).Select

ActiveCell.Value = "Adding data to row number: " & X

Next X

Range("A26").Select

ActiveCell.Value = " "

Range("A27").Select

ActiveCell.Value = " "" to create a loop that will add data to the first 25 rows. Add a "space" to the last two rows.

4

Type "lastRow = ActiveSheet.UsedRange.Rows.Count" to get the last row.

Type "MsgBox." The last row on this worksheet is: " & lastRow" to display the last row through a message box.

5

Type "End Sub" if necessary. Run your new sub and you will see a message box with "The last row on this worksheet is: 27".

Tips

  • check Your new sub should look like this:
  • check "Sub goToLastRow()
  • check Dim lastRow As Integer
  • check Dim X As Integer
  • check For X = 1 To 25
  • check Range("A" & X).Select
  • check ActiveCell.Value = "Adding data to row number: " & X
  • check Next X
  • check Range("A26").Select
  • check ActiveCell.Value = " "
  • check Range("A27").Select
  • check ActiveCell.Value = " "
  • check lastRow = ActiveSheet.UsedRange.Rows.Count
  • check MsgBox "The last row on this worksheet is: " & lastRow
  • check End Sub"

About the Author

Jaime Avelar is a professional writer whose programming articles appear on various websites. He has been a software programmer since 2000. Avelar holds a Master of Science in information systems from the University of Texas at Arlington.

Photo Credits

  • photo_camera number background image by kuhar from Fotolia.com