Microsoft Knowledge Base

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows 95, version 7.0

SUMMARY

In Microsoft Excel, the Visual Basic statement Selection.Rows.Count, which returns the number of rows in the current selection, will return an incorrect result if the current selection is nonadjacent.

This incorrect result occurs because the Selection.Rows.Count statement only counts the number of rows in the first Area of the selection. An Area is defined as a single piece of a nonadjacent selection. For example, the selection $1:$2, $4:$6, $8:$11 consists of three Areas: Selection.Rows.Count will only count rows in the first Area (the range $1:$2).

The above information also applies to Selection.Columns.Count, which counts the number of columns in the current selection (or first Area of a nonadjacent selection).

The Visual Basic code example shown below demonstrates one way to return the correct number of rows (or columns) in a nonadjacent selection.

MORE INFORMATION

You can create a nonadjacent selection by selecting one range and then selecting another range while holding down the CTRL key or using Microsoft Excel version 4.0 or Visual Basic commands that select two or more ranges at the same time.

To count the number of rows or columns in a selection when the selection is nonadjacent, you must separate the selection into Areas. This process is illustrated in the Visual Basic code example below.

Visual Basic Code Example

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

NOTE: To convert this example to work with columns, substitute "Column" for "Row."

To use the CountRows subroutine, select any combination of rows on a worksheet. Then, choose Macro from the Tools menu, select CountRows from the list of macro names, and choose Run.

'----------------------------------------------------------------------

Sub CountRows()
   'Dimension some variables.
   Dim Counter As Integer, NumRows As Integer
   Dim X As Variant, Y As Variant
   NumRows = 0                             'initialize NumRows
   'Sets range X equal to the current selection.
   Set X = Selection
   'Initializes range Y equal to the first Area in X.
   Set Y = X.Areas(1)
   'Iterate through the loop once for each Area (nonadjacent piece)of
   'the range X. This loop consolidates the different Areas in order to
   'prevent row miscounts due to overlapping Areas.
   For Counter = 1 To X.Areas.Count
      'Set Y equal to the union of its previous range and the range of
      'the rows which encompass the current Area.
      Set Y = Application.Union(Y, X.Areas(Counter).EntireRow)
   Next                                  'loop until all done
   'Iterate through the loop once for each Area (nonadjacent piece)of
   'the range Y. This loop actually counts the rows.
   For Counter = 1 To Y.Areas.Count
      'Set NumRows equal to its previous value plus the number of rows
      'in the current Area.
      NumRows = NumRows + Y.Areas(Counter).Rows.Count
   Next                                  'loop until all done
   'Show the number of rows.
   MsgBox Str(NumRows) & " rows selected."
End Sub
'----------------------------------------------------------------------

   9 rows selected.