Monday, October 10, 2011

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.
Olá! Se você ainda não assinou, assine nosso RSS feed e receba nossas atualizações por email, ou siga nos no Twitter.
Nome: Email:

6 kommentarer:

Anonymous said... at June 20, 2012 at 12:17 PM

Thers a "Set" missing in
objCell = objSelectionArea.Rows(intRow)
it shuld be
set objCell = objSelectionArea.Rows(intRow)

and
dim intRow as Long
dim objCell as Object 'as Range

Ulf said... at September 5, 2012 at 10:03 AM

Thank you for your feedback. The article as been updated.

MADDI4U said... at October 29, 2012 at 1:55 PM

am getting run-time error 91
Object variable or with block variable not set on
strName = objNameRange(intActualRow, 2).Value

marcnz said... at February 11, 2013 at 6:12 AM

I would think the original code has a typo as the only integer declared is called intRow and not intActualRow.

Try changing that and see what result you have.

Anonymous said... at February 13, 2013 at 1:43 PM

exactly what i was looking for
thank you :)

Gangaa Do said... at December 9, 2013 at 11:37 AM

m getting run-time error 91
Object variable or with block variable not set on
strName = objNameRange(intActualRow, 2).Value

Post a Comment