How to Select Every Other Row in Excel


This post will guide you how to select every other row with VBA macro in Excel. How do I select every nth row so that you can delete them in Excel. How to highlight every other row in Excel.

Select Every Other Row

Assuming that you have a list of data in range A1:B9, you want to select every second row in your worksheet. How to achieve it. You can use an Excel VBA Macro to achieve the result quickly. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

select every other row1

Sub SelectEveryOtherRows()
    Dim dCell As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select a Range that you want to select every other rows:", "SelectEveryOtherRows", myRange.Address, Type:=8)
    rowS = Application.InputBox("Please type a number:", "SelectEveryOtherRows", Type:=1)
    For i = 1 To myRange.Rows.Count Step rowS + 1
        Set myCell = myRange.Cells(i, 1)
        If dCell Is Nothing Then
            Set dCell = myCell
            Set dCell = Application.Union(dCell, myCell)
        End If
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

select every other row2

#6 Select a Range that you want to select every other rows. click Ok button.

select every other row3

#7 Please type a number. click Ok button.

select every other row4

#8 let’s see the result:

select every other row5

After every second rows are selected, you can try to format/delete/copy those selected rows. If you want to highlight every second rows, just keep to do the following steps:

Video: Select Every Other Row

See Also:


Leave a Reply