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

Written by louise jones
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How do I Count the Rows in an Excel Spread Sheet Using a VBA Macro?
Macros are used to automate tasks in Excel. (computer image by fotografiche.eu from Fotolia.com)

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.

Skill level:
Easy

Other People Are Reading

Instructions

  1. 1

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

  2. 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. 3

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

  4. 4

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

  5. 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, my Range As Range
    
    Set my Range = 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. 6

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

  7. 7

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

  8. 8

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

Tips and 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.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.