VBA Rows.Count in Selection

Question

I'm looking to work out how many rows a user has selected to be displayed at the top of the sheet next to an action button, I.e. Button says "Generate Email" and next to it says "x items selected".

As this is updated everytime the selection is changed, I have the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Sheet1.Range("E1") = Target.Rows.Count & " items selected"
End Sub

This works fine if the user selects consecutive rows, for e.g. 7:10 returns 4.

My problem is if a user selected rows 7, and 10. It would only return 1 (the rows in the first part of the selection).

From what I've found, there is no way of just getting this value from a property, but I can't get my head around how to iterate through all parts of the selection/target and calculate the sum of rows. Then there is also the possibility that the user selects say A7, C7, and A10. A7 and C7 relate to the same item, so this should only really be treated as one, not two, which I think my hypothetical code would do...

Has anyone tried to achieve this before and been successful or could point me in the direction of some properties which may help? I tried a separate function to achieve it, but that wasn't working either.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Sheet1.Range("E1") = getRowCount(Target) & " items selected"
End Sub

Function getRowCount(selectedRanges As Ranges)
  rowCount = 0
  For Each subRange In selectedRanges
    rowCount = rowCount + subRange.Rows.Count
  Next

  getRowCount = rowCount
End Function

Answer 1

I think this will work. (Did when I tried it.)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Create a range containing just column A
    Dim subRange As Range
    Dim r As Range
    For Each subRange In Target.Areas
        If r Is Nothing Then
            Set r = subRange.EntireRow.Columns(1)
        Else
            Set r = Union(r, subRange.EntireRow.Columns(1))
        End If
    Next
    'Count how many cells in the combined column A range
    Sheet1.Range("E1") = r.Cells.Count & " items selected"
End Sub

Answer 2

Sub NumberOfRowsSelected()
Dim vMatch As Variant, aRows() As Long, r As Range, x As Long

ReDim Preserve aRows(x)
aRows(x) = 0

For Each r In Selection.Cells
    vMatch = Application.Match(r.Row, aRows, 0)

    If IsError(vMatch) Then
        x = x + 1
        ReDim Preserve aRows(0 To x)
        aRows(x) = r.Row
    End If
Next r

MsgBox UBound(aRows)

End Sub

Revised Code Converted as Function

Sub NumberOfRowsSelected()
    MsgBox RowsCount(Selection)
End Sub

Function RowsCount(rRange As Range) As Long
Dim vMatch As Variant, aRows() As Long, r As Range, x As Long

ReDim Preserve aRows(x)
aRows(x) = 0

For Each r In rRange.Cells
    vMatch = Application.Match(r.Row, aRows, 0)

    If IsError(vMatch) Then
        x = x + 1
        ReDim Preserve aRows(0 To x)
        aRows(x) = r.Row
    End If
Next r

RowsCount = UBound(aRows)

End Function

Answer 3

A different method, building up a string of checked rows seems pretty straight-forward to avoid double counting. See comments for details:

Function getRowCount(rng As Range) As Long
    Dim c As Range
    ' Keep track of which rows we've already counted
    Dim countedrows As String: countedrows = ","
    ' Loop over cells in range
    For Each c In rng
        ' Check if already counted
        If Not InStr(countedrows, "," & c.Row & ",") > 0 Then
            ' Add to counted list
            countedrows = countedrows & c.Row & ","
        End If
    Next c
    ' Get number of rows counted
    Dim rowsarr() As String: rowsarr = Split(countedrows, ",")
    getRowCount = UBound(rowsarr) - LBound(rowsarr) - 1
End Function

Answer 4

You need to count the rows in each Area the user has selected.
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-areas-property-excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim rArea As Range
    Dim lCount As Long

    For Each rArea In Selection.Areas
        lCount = lCount + rArea.Rows.Count
    Next rArea

    Sheet1.Range("E1") = lCount

End Sub

Answer 5

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
Dim i, currentRow As Long: i = 0
'get row of first cell in range
currentRow = Target.Cells(1, 1).row

For Each cell In Target
    'if row is different, then increase number of items, as it's next item
    If Not currentRow = cell.row Then
       i = i + 1
       currentRow = cell.row
    End If
Next cell

Range("E1").Value = i

End Sub