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.
