How to programmatically select every nth row in a range in Excel

Article translations Article translations
Article ID: 213438 - View products that this article applies to.
This article was previously published under Q213438
Expand all | Collapse all

SUMMARY

In Microsoft Excel, you can create a Microsoft Visual Basic for Applications macro to select every nth row in a currently selected contiguous range on a worksheet. This article describes a sample macro that performs this procedure.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. To create and use a Visual Basic macro that selects every third row in a contiguous range, follow these steps:

Note 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 to 4.
  1. Start Excel, and then press ALT+F11 to start the Visual Basic Editor.
  2. On the Insert menu, click Module.
  3. In the module sheet, type or paste the following sample code:
    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
    					
  4. Press ALT+F11 to return to Excel.
  5. Type data in cells A1:E9.
  6. Select cells A1:E9.
  7. On the Tools menu, point to Macro, and then click Macros.
  8. In the Macro name list, click SelectEveryNthRow, and then click Run. Note that rows three, six, and nine are selected.

Properties

Article ID: 213438 - Last Review: January 24, 2007 - Revision: 7.4
APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 98 for Macintosh
Keywords: 
kbdtacode kbhowto kbinfo kbprogramming KB213438

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com