Microsoft KB Archive/125267

= Macro to Select Every nth Row/Column =

PSS ID Number: 125267

Article Last Modified on 8/15/2003

-

The information in this article applies to:


 * Microsoft Excel 97 for Windows
 * Microsoft Excel for Windows 95 7.0
 * Microsoft Excel for Windows 5.0
 * Microsoft Excel 98 Macintosh Edition
 * Microsoft Visual Basic for Applications

-



This article was previously published under Q125267





SUMMARY
In Microsoft Excel, you can create a Microsoft Visual Basic for Applications macro (Sub procedure) to select every nth row within a currently selected contiguous range on a worksheet. An example of such a procedure is provided below.



MORE INFORMATION
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 a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

The following macro selects every third row in the current selection. To change the number of rows between each selected row, change the value of the RowsBetween variable (for example, if you want to select every fourth row, set RowsBetween equal to 4 ).

NOTE: You must have a contiguous range of cells selected before you run this macro.

Sample Visual Basic Procedure
Sub SelectEveryNthRow

' Initialize ColsSelection equal to the number of columns in the ' selection. ColsSelection = Selection.Columns.Count

' Initialize RowsSelection equal to the number of rows in your ' selection. RowsSelection = Selection.Rows.Count

' Initialize RowsBetween equal to three. RowsBetween = 3

' Initialize Diff equal to one row less than the first row number of      ' the selection. Diff = Selection.Row - 1

' Resize the selection to be 1 column wide and the same number of      ' rows long as the initial selection. Selection.Resize(RowsSelection, 1).Select

' Resize the selection to be every third row and the same number of      ' columns wide as the original selection. Set FinalRange = Selection. _         Offset(RowsBetween - 1, 0).Resize(1,ColsSelection)

' Loop through each cell in the selection. For Each xCell In Selection

' If the row number is a multiple of 3, then. . .          If xCell.Row Mod RowsBetween = Diff Then ' ...reset FinalRange to include the union of the current ' FinalRange and the same number of columns. Set FinalRange = Application.Union _ (FinalRange, xCell.Resize(1,ColsSelection)) ' End check. End If      ' Iterate loop. Next xCell ' Select the requested cells in the range. FinalRange.Select End Sub For an example of a similar Microsoft Excel 4.0 macro, please see the following article in the Microsoft Knowledge Base:

103165 Excel: Macro to Select a Range Every nth Row/Column

Additional query words: XL97 XL98 XL7 XL5 XL

Keywords: kbdtacode kbhowto kbProgramming KB125267

Technology: kbExcel500 kbExcel95 kbExcel95Search kbExcel97Search kbExcel98 kbExcel98Search kbExcelMacsearch kbExcelSearch kbExcelWinSearch kbHWMAC kbOSMAC kbVBASearch kbZNotKeyword3 kbZNotKeyword6

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© 2004 Microsoft Corporation. All rights reserved.