XL: Range.EntireRow May Return Incorrect Result

ID: Q108518

The information in this article applies to:

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

SYMPTOMS

In the versions of Microsoft Excel listed at the beginning of this article, the Range.EntireRow method may return an incorrect result if the Range is a nonadjacent selection. For example, if the current Range is the nonadjacent selection $A$1:$A$3, $C$11:$C$13, the Range.EntireRow method will return $1:$13, not $1:$3,$11:$13 as would be expected. This also occurs when you use the Range.EntireColumn method to return entire columns when the Range is nonadjacent.

WORKAROUND

To return the correct rows or columns in a selection when the selection is nonadjacent, you must separate the selection into Areas, where an Area is defined as a single piece of a nonadjacent selection. This process is illustrated in the Visual Basic code example below.

Visual Basic Code Example

Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

NOTE: To convert this example to work with columns, use "Column" in place of "Row".

To use the SelectRows Sub procedure, select any combination of cells on Sheet1. (This example assumes the current selection is $A$1:$A$3,$C$11:$C$13 on Sheet1.) Then, click Macro on the Tools menu, select the name SelectRows from the list of macro names, and click Run.


     Option Explicit

   Sub SelectRows()

      ' Dimension some variables.
      Dim Counter As Integer, X As Variant, Y As Variant

      Sheets("Sheet1").Activate          ' Ensure the worksheet is active.

      ' 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.
      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 done.

      Y.Select                            ' Select the range Y.

   End Sub

For example, if you select the range $A$1:$A$3,$C$11:$C$13 on worksheet Sheet1 and then run the SelectRows Sub procedure, the procedure determines how many Areas there are in the selection (there are two: $A$1:$A$3 and $C$11:$C$13), determines which combination of rows encompasses each Area ($1:$3, $11:$13), and selects the range $1:$3,$11:$13.

STATUS

Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed at the beginning of this article. This problem was corrected in Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition.

MORE INFORMATION

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

Additional query words: 5.00a 5.00c 7.00a XL7 XL5 Selection.EntireRow Selection.EntireColumn

Keywords : kbprg PgmOthr PgmHowto
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0,7.0a; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbbug