How do I Count the Rows in an Excel Spread Sheet Using a VBA Macro?

Macros are used to automate tasks in Excel.

Using macros can speed up actions in Excel that need to be done time after time by automating them. Macros can also be used to gather data that may be needed for further programming. For example, the number of cells or rows in a table may be needed to ensure that automated data manipulation functions as intended. To demonstrate, a simple macro can count the number of rows in a spreadsheet and open a message box stating that number.

Instructions

1. Open an new excel spreadsheet and fill in the first few cells in column B with random words, letters or numbers.

2. Click on the "Developer" tab on Excel's menu ribbon. If you cannot see the "Developer" tab on the menu ribbon, click on the Microsoft Office icon at the top left of the window, click on the "Excel Options" button, and click in the tick box that reads "Show Developer Tab in the Ribbon."

3. Launch the Visual Basic Editor by clicking on the Visual Basic icon or pressing Alt and F11 simultaneously.

4. Click on "Insert" on the Visual Basic Editor menu and chose "Module."

5. Type the following into the module box:

   Sub CountNumberofRowsinColumnB()

   NonBlankRange ("Sheet1!B:B")

   End Sub

   Sub NonBlankRange(sRange As String)

   Dim countNonBlank As Integer, myRange As Range

   Set myRange = Range(sRange)

   countNonBlank = Application.WorksheetFunction.CountA(myRange)

   MsgBox "Number of Rows: " & countNonBlank, , sRange

   End Sub

   This code was adapted from a project in "Microsoft Visual Basic .Net Step by Step" cited below.

6. Click on the Excel icon on the Visual Basic Editor to show the Excel spreadsheet.

7. Click on the Macro icon on the Excel ribbon and select "CountNumberofRowsinColumnB" from the macro name box.

8. Click "Run" in the macro box. A message box will open that tells you how many rows have been filled in.

Tips & Warnings

• Another way to count rows is to use the "Count" function. Microsoft gives the following code snippet in the Excel 2003 VBA Language Reference cited below. To use the Count function:

• Sub DisplayRowCount()

• Dim iAreaCount As Integer

• Dim i As Integer

• Worksheets("Sheet1").Activate

• iAreaCount = Selection.Areas.Count

• If iAreaCount <= 1 Then

• MsgBox "The selection contains " & Selection.Rows.Count & " rows."

• Else

• For i = 1 To iAreaCount

• MsgBox "Area " & i & " of the selection contains " & _

• Selection.Areas(i).Rows.Count & " rows."

• Next i

• End If

• End Sub

• This needs to have the area to be counted selected before the macro is run, so if only one cell is selected the answer it gives will be "1" and if the whole spreadsheet is selected it will read "1048576" (in Excel 2007).

• Use macros in test conditions first, by using copies of the spreadsheets to test macros on, in order to save losing any important data.

References

• MSDN: Count Property [Excel 2003 VBA Language Reference]
• Spreadsheetpage.com: Cell Counting Techniques
• "Microsoft Visual Basic.Net Step by Step"; Michael Halvorson; 2003
• "Developing Windows-Based Applications"; Matthew A. Stoecker; 2003