Count of rows - discontiguous selection
 

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...

Count of rows - discontiguous selection
 

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."

--
Rick (MVP - Excel)


"xp" wrote in message
...
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...

Count of rows - discontiguous selection
 

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

--
Gary''s Student - gsnu200907


"xp" wrote:

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...

Count of rows - discontiguous selection
 

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.



xp wrote:

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...

--

Dave Peterson

Count of rows - discontiguous selection
 

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

--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200907


"xp" wrote:

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...