Count no. of rows only from Selection area in xla

[AHAMED ADNAN]

I need vba code for geting number of rows in randomly selected reigon only. please note selected region could be randomly selected. only i need the no of rows

 

[tyija1995]

Hi Ahamed,

Is this something you're looking for?

Sub NumRows()
    Dim rng As Range
    Dim rowsSelected As Integer
    
    Set rng = Selection
    rowsSelected = rng.Rows.Count
    MsgBox rowsSelected
    
End Sub

 

[footoo]

If the selection is not contiguous :

Sub v()
Dim rng As Range, a As Range, c&
For Each a In Selection.Areas
    If rng Is Nothing Then
        Set rng = a
    Else: Set rng = Union(rng, a)
    End If
Next
For Each a In Intersect([A:A], rng.EntireRow).Areas
    c = c + a.Rows.Count
Next
MsgBox c
End Sub

 

[footoo]

Shorter code :

Sub v()
Dim a As Range, c&
For Each a In Intersect([A:A], Selection.EntireRow).Areas
    c = c + a.Rows.Count
Next
MsgBox c
End Sub