How To Count Number Of Rows In Excel Vba

by

Counting Number of Rows in Excel VBA

To count the number of rows in Excel using VBA, you can use the following code:


   Sub CountRows()
       Dim rowCount As Integer
       
       ' Activate the desired worksheet
       Sheets("Sheet1").Activate
       
       ' Get the last row number with data in column A
       rowCount = Cells(Rows.Count, 1).End(xlUp).Row
       
       ' Display the result
       MsgBox "Number of rows: " & rowCount
   End Sub

In this example, we define a variable called rowCount to store the number of rows. Next, we activate the worksheet “Sheet1” where we want to count the rows. With the line Cells(Rows.Count, 1).End(xlUp).Row, we find the last row with data in column A. This is achieved by starting from the last row of the worksheet (Rows.Count) and moving upwards until it finds the last used cell (xlUp). Finally, we display the result in a message box.

You can modify this code to fit your specific needs. For example, if you want to count the number of rows in a different column, you can replace the “1” in Cells(Rows.Count, 1).End(xlUp).Row with the desired column number or use a variable to store it.

Leave a comment