I am highlighting rows beyond what I see on screen. Can someone give me a
macro that counts the number of highlighted rows.

Sub HowMany()
MsgBox Selection.Rows.Count
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Phil H" wrote:

> I am highlighting rows beyond what I see on screen. Can someone give me a
> macro that counts the number of highlighted rows.

By "highlighting", do you mean "selecting"? If so, then give this macro a
try (it will handle both contiguous and non contiguous selections)...

Sub CountRowsInSelection()
Dim A As Range, U As Range
For Each A In Selection.Areas
If U Is Nothing Then
Set U = A.EntireRow
Else
Set U = Union(U, A.EntireRow)
End If
Next
MsgBox Intersect(U, Columns(1)).Count
End Sub

--
Rick (MVP - Excel)


"Phil H" <(E-Mail Removed)> wrote in message
news:FCB4462D-FD45-4EC9-892F-(E-Mail Removed)...
>I am highlighting rows beyond what I see on screen. Can someone give me a
> macro that counts the number of highlighted rows.

Your code will not work if the Selection contains non contiguous areas with
non overlapping rows (for example, C3:F9 and H14:M20).

--
Rick (MVP - Excel)


"Luke M" <(E-Mail Removed)> wrote in message
news:36C0418F-099F-4D77-AE16-(E-Mail Removed)...
> Sub HowMany()
> MsgBox Selection.Rows.Count
> End Sub
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Phil H" wrote:
>
>> I am highlighting rows beyond what I see on screen. Can someone give me
>> a
>> macro that counts the number of highlighted rows.

Works great. Thanks, Luke.

"Luke M" wrote:

> Sub HowMany()
> MsgBox Selection.Rows.Count
> End Sub
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Phil H" wrote:
>
> > I am highlighting rows beyond what I see on screen. Can someone give me a
> > macro that counts the number of highlighted rows.

I'm guessing, then, that you will never have selections composed of
non-contiguous areas. For future consideration, you should really mention
such things in your questions when you ask them... the more information you
give about your set up and what you want to do, the better able we are to
give you solutions that do what you are looking for.

--
Rick (MVP - Excel)


"Phil H" <(E-Mail Removed)> wrote in message
news:F69D5E8D-E762-4312-A114-(E-Mail Removed)...
> Works great. Thanks, Luke.
>
> "Luke M" wrote:
>
>> Sub HowMany()
>> MsgBox Selection.Rows.Count
>> End Sub
>> --
>> Best Regards,
>>
>> Luke M
>> *Remember to click "yes" if this post helped you!*
>>
>>
>> "Phil H" wrote:
>>
>> > I am highlighting rows beyond what I see on screen. Can someone give
>> > me a
>> > macro that counts the number of highlighted rows.

If highlighting rows means selecting entire rows, I think this would be
enough.

MsgBox Intersect(Selection, Columns(1)).Count

If not entire rows, then

MsgBox Intersect(Selection.EntireRow, Columns(1)).Count

Keiji

Rick Rothstein wrote:
> By "highlighting", do you mean "selecting"? If so, then give this macro
> a try (it will handle both contiguous and non contiguous selections)...
>
> Sub CountRowsInSelection()
> Dim A As Range, U As Range
> For Each A In Selection.Areas
> If U Is Nothing Then
> Set U = A.EntireRow
> Else
> Set U = Union(U, A.EntireRow)
> End If
> Next
> MsgBox Intersect(U, Columns(1)).Count
> End Sub
>

> If not entire rows, then
>
> MsgBox Intersect(Selection.EntireRow, Columns(1)).Count

Yes, that works fine and is **much** better than the code I posted!

--
Rick (MVP - Excel)


"keiji kounoike" <"kounoike A | T ma.Pikara.ne.jp"> wrote in message
news:OOwt$(E-Mail Removed)...
> If highlighting rows means selecting entire rows, I think this would be
> enough.
>
> MsgBox Intersect(Selection, Columns(1)).Count
>
> If not entire rows, then
>
> MsgBox Intersect(Selection.EntireRow, Columns(1)).Count
>
> Keiji
>
> Rick Rothstein wrote:
>> By "highlighting", do you mean "selecting"? If so, then give this macro a
>> try (it will handle both contiguous and non contiguous selections)...
>>
>> Sub CountRowsInSelection()
>> Dim A As Range, U As Range
>> For Each A In Selection.Areas
>> If U Is Nothing Then
>> Set U = A.EntireRow
>> Else
>> Set U = Union(U, A.EntireRow)
>> End If
>> Next
>> MsgBox Intersect(U, Columns(1)).Count
>> End Sub
>>