Excel VBA: Get all the Selected rows

Microsoft ExcelLeave a Comment on Excel VBA: Get all the Selected rows

Excel VBA: Get all the Selected rows

If the user selects multiple rows in Microsoft 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.

Related

Ulf Emsoy has long working experience in project management, software development and supply chain management.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top