Sometimes, you need to know the number of rows being used in an Excel spreadsheet, either for a formula or sometimes even a VBA Macro. Fortunately, there are a few easy ways to accomplish this, and Excel VBA is just one of them. The simplest way to find out this information is to scroll to the bottom of your spreadsheet and notice the number to the right of the last data point, but to do it through VBA is also fairly simple.
Open your Excel spreadsheet and open the VBA editor by pressing "Alt+F11."
Start a new VBA project file by right-clicking your workbook on the right-hand menu and choosing the "New" option.
Start the new function by typing "Sub CountRows()." This informs the VBA compiler that there is a new subroutine in the project file. If you'd prefer to give the subroutine another name, simply change "CountRows" to whatever you want it to say, but it must have the open and closed parenthesis behind it, as in "YourName ()".
Create a variable by including "Dim Count as Long" in the next line.
Type in the following code:
Count = 0 Do Count = Count + 1 ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, 1))
This tells the compiler program to first clear out the Count variable, then start moving through the spreadsheet. It adds one to the total Count, then moves down to the next cell, as long as the next cell is empty. When it reaches an empty cell, it will stop running.
Bring up a message box by adding a new line with "MsgBox Count" in it. This will display a pop-up box with the total number of rows in it. To make the Message box seem a little more helpful, you can add some more text to it. Change the line to read as follows:
MsgBox "There are" Count "Rows"
You can change the text between quotation marks to way whatever you'd like it to be.
Finally, type "End Sub" in a new line to close the function out.
Return to your Excel spreadsheet, and save it.
Highlight the first cell in the column with the most data. This will be a column that has no empty cells between the top and the bottom.
Open the "Tools" menu and select "Macros" to bring up macros dialogue with all the macros programmed into the spreadsheet.
Select your macro from Section 1, "CountRows" or whatever name you gave it. Click "Run." When complete, the Macro will pop up with the message box report of the number of rows you've filled.
It may be faster and simpler, if all you need is a number, to count the rows using a formula. In a blank cell, type "=COUNT(A:A, IF(A>0,1,0))", where A is the column which you're counting.