Prev: Hyperlink opens word template for editting, not as new document
Next: File Name and Full Path Name
From: Phil H on 28 Jan 2010 14:51
Need to modify this maco to not count hidden rows. For example if seven rows
of ten rows in question are hidden, I need the macro to count only the three
visible on screen.

Also, if no rows were hidden, and all ten were selected (highlighted), the
macro should count ten.

In other words, it should count only what is selected/visible on screen.

Sub CountHighlightedRows()
MsgBox "Rows Selected: " & Selection.Rows.Count
End Sub
From: JLGWhiz on 28 Jan 2010 14:58
Not sure if I understand, but try this:

Sub fj()
Dim x As Long
x = Selection.SpecialCells(xlCellTypeVisible).Count
MsgBox x
End Sub


"Phil H" <PhilH(a)discussions.microsoft.com> wrote in message
news:CE24AAB5-B94A-4ADE-9021-10C337D849FC(a)microsoft.com...
> Need to modify this maco to not count hidden rows. For example if seven
> rows
> of ten rows in question are hidden, I need the macro to count only the
> three
> visible on screen.
>
> Also, if no rows were hidden, and all ten were selected (highlighted), the
> macro should count ten.
>
> In other words, it should count only what is selected/visible on screen.
>
> Sub CountHighlightedRows()
> MsgBox "Rows Selected: " & Selection.Rows.Count
> End Sub


From: JLGWhiz on 28 Jan 2010 15:07
I noticed an anomaly about using the xlVixible property to count the rows.
If you use the Rows.Count, it only counts to down to the first hidden row.
But if you only select one column and count the special cells visible
property, it give the correct count of rows not hidden.


"Phil H" <PhilH(a)discussions.microsoft.com> wrote in message
news:CE24AAB5-B94A-4ADE-9021-10C337D849FC(a)microsoft.com...
> Need to modify this maco to not count hidden rows. For example if seven
> rows
> of ten rows in question are hidden, I need the macro to count only the
> three
> visible on screen.
>
> Also, if no rows were hidden, and all ten were selected (highlighted), the
> macro should count ten.
>
> In other words, it should count only what is selected/visible on screen.
>
> Sub CountHighlightedRows()
> MsgBox "Rows Selected: " & Selection.Rows.Count
> End Sub


From: Ryan H on 28 Jan 2010 15:14
This will count all the visible rows in your selection. Hope this helps! If
so, let me know, click "YES" below.

Option Explicit

Sub CountHighlightedRows()

Dim rw As Range
Dim VisibleRows As Long

For Each rw In Selection.Rows
If rw.Hidden = False Then
VisibleRows = VisibleRows + 1
End If
Next rw

MsgBox "Rows Selected: " & VisibleRows, vbInformation

End Sub
--
Cheers,
Ryan


"Phil H" wrote:

> Need to modify this maco to not count hidden rows. For example if seven rows
> of ten rows in question are hidden, I need the macro to count only the three
> visible on screen.
>
> Also, if no rows were hidden, and all ten were selected (highlighted), the
> macro should count ten.
>
> In other words, it should count only what is selected/visible on screen.
>
> Sub CountHighlightedRows()
> MsgBox "Rows Selected: " & Selection.Rows.Count
> End Sub
From: JLGWhiz on 28 Jan 2010 15:13
This is sort of a gerry rig but it works and will do what you want.

Sub fl()
Dim x As Long
x = Selection.SpecialCells(xlCellTypeVisible) _
.Count / Selection.Columns.Count
MsgBox x
End Sub




"JLGWhiz" <JLGWhiz(a)cfl.rr.com> wrote in message
news:OCC8AXFoKHA.1544(a)TK2MSFTNGP02.phx.gbl...
>I noticed an anomaly about using the xlVixible property to count the rows.
>If you use the Rows.Count, it only counts to down to the first hidden row.
>But if you only select one column and count the special cells visible
>property, it give the correct count of rows not hidden.
>
>
> "Phil H" <PhilH(a)discussions.microsoft.com> wrote in message
> news:CE24AAB5-B94A-4ADE-9021-10C337D849FC(a)microsoft.com...
>> Need to modify this maco to not count hidden rows. For example if seven
>> rows
>> of ten rows in question are hidden, I need the macro to count only the
>> three
>> visible on screen.
>>
>> Also, if no rows were hidden, and all ten were selected (highlighted),
>> the
>> macro should count ten.
>>
>> In other words, it should count only what is selected/visible on screen.
>>
>> Sub CountHighlightedRows()
>> MsgBox "Rows Selected: " & Selection.Rows.Count
>> End Sub
>
>


 |  Next  |  Last
Pages: 1 2
Prev: Hyperlink opens word template for editting, not as new document
Next: File Name and Full Path Name