Count of rows - discontiguous selection

  • Thread starter Thread starter xp
  • Start date Start date
X

xp

Hi,

I need a code that will count the number of rows in a selection; BUT, the
selection has multiple sets of discontiguous ranges of cells each with a
various number of rows.

I thought, Selection.Rows.Count would do it, but it doesn't...

Thanks for the help...
 
Try this code...

Dim R As Range, TotalRows As Long
......
......
For Each R In Selection.Areas
TotalRows = TotalRows + R.Rows.Count
Next
MsgBox "That selection has " & TotalRows & " rows in it."
 
Try this:

Sub IAmTheCount()
ic = 0
irlast = 0
For Each r In Selection
ir = r.Row
If ir <> irlast Then
ic = ic + 1
irlast = ir
End If
Next

MsgBox ic
End Sub
 
Dim myRng As Range
Set myRng = Intersect(ActiveSheet.Columns(1), Selection.EntireRow)
MsgBox myRng.Cells.Count

If the discontiguous areas could be in overlapping rows, this may not give you
want you want.

A1:A13, B2:B5, C12:C13

would give 13.
 
Here is another variation

AreaNo = 1
Msg = ""
For Each Area In Selection.Areas
Msg = Msg & "The area " & AreaNo & " selection contains " & _
Area.Rows.Count & " rows." & vbCrLf
AreaNo = AreaNo + 1

Next Area

MsgBox Msg
 
Back
Top