Problem with script to programmatically select every nth row in a range

[ryanl]

Hi all,
I searched the board and couldn't quite find anything that addresses this so I apologize if it is already solved.

I am using a script posted on Microsoft's website to programmatically select every nth row in a range in Excel here. I have posted the code below:

For the most part, it works very well. The problem occurs when I try to run the script when I have selected any number of rows starting below row 1 - then only the first third row is selected, regardless of the size of the range.

I have looked at the code, but can't see where the problem is. Can anyone find it? Thanks!

Ryan

***CODE START***


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

***CODE END***

 

[Norie]

Ryan

What do you have selected when you run the code?

And why do you want to select these rows?

 

[xld]

This seems to work. I have removed all of the garbage comments (if ever there is an argument against commenting your code, that is it), and make no excuses for teh code, it is rubbishm, but there you are

Sub SelectEveryNthRow()
    ColsSelection = Selection.Columns.Count
    RowsSelection = Selection.Rows.Count
    RowsBetween = 3
    
    Diff = Selection.Row Mod RowsBetween - 1

    Selection.Resize(RowsSelection, 1).Select
    
    Set finalrange = Selection. _
    Offset(RowsBetween - 1, 0).Resize(1, ColsSelection)
    
    For Each xCell In Selection
        If xCell.Row Mod RowsBetween = Diff Then
            Set finalrange = Application.Union _
            (finalrange, xCell.Resize(1, ColsSelection))
        End If
    Next xCell
    finalrange.Select
End Sub

 

[xld]

ACtually, there is still a bug

Sub SelectEveryNthRow()
    ColsSelection = Selection.Columns.Count
    RowsSelection = Selection.Rows.Count
    RowsBetween = 3
    
    Diff = Selection.Row Mod RowsBetween - 1
    If Diff = -1 Then Diff = RowsBetween - 1

    Selection.Resize(RowsSelection, 1).Select
    
    Set finalrange = Selection. _
    Offset(RowsBetween - 1, 0).Resize(1, ColsSelection)
    
    For Each xCell In Selection
        If xCell.Row Mod RowsBetween = Diff Then
            Set finalrange = Application.Union _
            (finalrange, xCell.Resize(1, ColsSelection))
        End If
    Next xCell
    finalrange.Select
End Sub

 

[ryanl]

Hi Norie,
As I said in my first post, it depends where my selection is: if I begin the selection on row 1, then it will select every third row - in this case. If I begin the selection below row 1, then I only get the first third row of that selection.

The reason is that I need to remove every third row from a selection that is usually hundreds of rows in size. The third row contains superfluous data.

Ryan

 

[Norie]

Ryan

If that's what you want to do then there's no need for that code.

Without seeing a sample of data it's hard to post other code but I'm pretty sure if you searched this site you'd find some.

One important thing to remember is when deleting you should work from the bottom up.

 

[ryanl]

Hi xld,
Wow, that was fast!

Sorry about the comments - I took it verbatim from Microsoft's site - thought they might be helpful Guess this is maybe why their products tend to have a lot of bugs ?

Thanks for the code! :D
I see you have posted two versions - the first version you sent seems to work - not sure where the bug is but I have copied both. This is really very helpful!

Ryan

 

[xld]

Typical MS bloat!

The bug in the first version if you start at a row number that when MODed with the rows between number gives 0. So starting at line 6 when the rows between is 3 will not work as required. I added a single line of code to handle this.