Macro to highlight every other row (regardless of range location)

[RacerGTX]

I'm currently using:

Sub SelectEveryNthRow()
ColsSelection = Selection.Columns.Count
RowsSelection = Selection.Rows.Count
RowsBetween = 2
Diff = Selection.Row - 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

...but I find it only works if the first row is within the same number of rows specified in RowsBetween.

For example, if RowsBetween = 2, and I select a range where the top row is greater than or equal to 3, it will not work properly, but instead select only one row; the row directly under the first row is selected in the desired range; the column count highlighted would be correct; just not the desired range of rows. Any ideas?

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Have you tried using a Table (formerly called a ListObject)?

Just select a cell in your data, hit Ctrl + T check the details in the dialog and hit Enter.
It will give you lots of other features as well.

 

[Rick Rothstein]

If you do not want to use RickXL's suggestion for some reason, then this macro will do what you asked for...

Sub SelectEveryNthRow()
  Dim X As Long, StartRow As Long, EndRow As Long, SkipCount As Long, Final As Range
  SkipCount = 2
  StartRow = Selection.Row
  EndRow = StartRow + Selection.Rows.Count - 1
  Set Final = Rows(StartRow)
  For X = StartRow + SkipCount To EndRow Step SkipCount
    Set Final = Union(Final, Rows(X))
  Next
  Intersect(Selection, Final).Select
End Sub

 

[RacerGTX]

Thanks Guys, the VB is exactly what I was looking for, and the table/ListObject looks very quick and useful as well.