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

--
Rick (MVP - Excel)


"xp" <(E-Mail Removed)> wrote in message
news2A91AF0-7368-4B0D-A7F2-(E-Mail Removed)...
> 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:

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

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

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