Excel VBA: Get all selected rows

If the user does does a selection of rows in Excel, the selection can contain multiple areas. An area is a subselection of rows created when the user presses the Shift button while doing a multiselect. In VBA we need to handle each area independently to be able to retrieve all the selected rows.

' Example
Public Sub Test()

    Dim objSelection As Range

    Dim objSelectionArea As Range

    Dim objCell As Range
    Dim intRow As Integer


    ' Get the current selection

    Set objSelection = Application.Selection

    ' Walk through the areas

    For Each objSelectionArea In objSelection.Areas

        ' Walk through the rows

        For intRow = 1 To objSelectionArea.Rows.Count Step 1

            ' Get the row reference

            Set objCell = objSelectionArea.Rows(intRow)

            ' Get the actual row index (in the worksheet).

            ' The other row index is relative to the collection.

            intActualRow = objCell.Row

            ' Get any cell value by using the actual row index

            ' Example:

            strName = objNameRange(intActualRow, 1).value

        Next

    Next

End Sub

In the example we have not defined the variables strName and objNameRange. For your reference the variables are defined as follows:

    Dim strName As String

    Dim objNameRange As Range

objNameRange can be any range in your selected worksheet.