Table of Content

I need to process some data for selected rows in vbscript, I don’t care the exactly selected area, only care about row numbers, so develop this function to fill this common purpose. The result returns as vb array, index starts from 0.

Few concepts here for selected area:

Selection: consists of SelectionArea

SelectionArea: there is only 1 area when selected rows continuously; otherwise there are number of areas when choice multi-rows by Ctrl+Click

Range: collection of objects, Selection, SelectionArea, Cell are range


Private Function selectedRows()

    Dim objSelection As Range
    Dim objSelectionArea As Range
    Dim objCell As Range
    Dim intRow As Integer
    Dim intActualRow As Integer
    Dim ArrIndex As Integer
    Dim selectedRowNum()

    ArrIndex = 0

    ' Get the current selection
    Set objSelection = Application.Selection
    'MsgBox objSelection.Areas.Count

    For Each objSelectionArea In objSelection.Areas
        ArrIndex = ArrIndex + objSelectionArea.Rows.Count
    Next

    'MsgBox ArrIndex

    ReDim selectedRowNum(ArrIndex - 1)
    'MsgBox UBound(selectedRowNum)

    ArrIndex = 0

    ' Walk through the areas
    For Each objSelectionArea In objSelection.Areas
        'MsgBox objSelectionArea
        ' Walk through the rows
        For intRow = 1 To objSelectionArea.Rows.Count Step 1
            ' Get the row reference
            'MsgBox intRow
            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:
            'MsgBox ArrIndex & "-" & intActualRow
            selectedRowNum(ArrIndex) = intActualRow
            'MsgBox "selectedRowNum(" & ArrIndex & ") - " & selectedRowNum(ArrIndex)
            ArrIndex = ArrIndex + 1
        Next
    Next
    selectedRows = selectedRowNum()
End Function

This is testing, printout array index and row number:


Private Sub selectedRowstest()

myselectedRows = selectedRows

'MsgBox UBound(myselectedRows)
Index = 0

For Each Row In myselectedRows
    MsgBox Index & "-" & Row & " " & myselectedRows(Index)
    Index = Index + 1
Next

End Sub

This script works well for single row as myselectedRows(0), so I don’t mind treat them same way.