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.