Function to display a count of visible cells/rows... doesn't

This is what happens when you take a couple of months away from VBA...

I'm trying to write a function to display the number of visible wows with a
range of cells.

Function CountVisibleRows(TheRange)
Dim CellToCount As Range
Dim RunningTotal As Long
RunningTotal = 0
For Each CellToCount In TheRange
If CellToCount.Row.Hidden = False Then
RunningTotal = RunningTotal + 1
End If
Next CellToCount
CountVisibleRows = RunningTotal
End Function

but I'm getting a message to say that .row is an invalid qualifier.
It's late in the day 9well, it is for me, anyway) and my brain has entered
the twilight zone.

Can anyone please help?

Thanks in advance

Pete

Peter,

The Row property returns the row number of a range, not a
reference to a row. Change

If CellToCount.Row.Hidden = False Then
to
If CellToCount.EntireRow.Hidden = False Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Peter Rooney" <[email protected]> wrote in
message
news:[email protected]...
> This is what happens when you take a couple of months away from
> VBA...
>
> I'm trying to write a function to display the number of visible
> wows with a
> range of cells.
>
> Function CountVisibleRows(TheRange)
> Dim CellToCount As Range
> Dim RunningTotal As Long
> RunningTotal = 0
> For Each CellToCount In TheRange
> If CellToCount.Row.Hidden = False Then
> RunningTotal = RunningTotal + 1
> End If
> Next CellToCount
> CountVisibleRows = RunningTotal
> End Function
>
> but I'm getting a message to say that .row is an invalid
> qualifier.
> It's late in the day 9well, it is for me, anyway) and my brain
> has entered
> the twilight zone.
>
> Can anyone please help?
>
> Thanks in advance
>
> Pete

Hi
..Rows instead of .Row

regards
Paul

Function CountVisibleRows(TheRange)
Dim CellToCount As Range
Dim RunningTotal As Long
RunningTotal = 0
For Each CellToCount In TheRange.Rows
If Not CellToCount.EntireRow.Hidden Then
RunningTotal = RunningTotal + 1
End If
Next CellToCount
CountVisibleRows = RunningTotal
End Function


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> This is what happens when you take a couple of months away from VBA...
>
> I'm trying to write a function to display the number of visible wows with
a
> range of cells.
>
> Function CountVisibleRows(TheRange)
> Dim CellToCount As Range
> Dim RunningTotal As Long
> RunningTotal = 0
> For Each CellToCount In TheRange
> If CellToCount.Row.Hidden = False Then
> RunningTotal = RunningTotal + 1
> End If
> Next CellToCount
> CountVisibleRows = RunningTotal
> End Function
>
> but I'm getting a message to say that .row is an invalid qualifier.
> It's late in the day 9well, it is for me, anyway) and my brain has entered
> the twilight zone.
>
> Can anyone please help?
>
> Thanks in advance
>
> Pete

EntireRow!!!
It's scary how quickly these things drop oput of the grey matter.
Thanks a million, Chip - you're a lifesaver!

Pete



"Chip Pearson" wrote:

> Peter,
>
> The Row property returns the row number of a range, not a
> reference to a row. Change
>
> If CellToCount.Row.Hidden = False Then
> to
> If CellToCount.EntireRow.Hidden = False Then
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Peter Rooney" <[email protected]> wrote in
> message
> news:[email protected]...
> > This is what happens when you take a couple of months away from
> > VBA...
> >
> > I'm trying to write a function to display the number of visible
> > wows with a
> > range of cells.
> >
> > Function CountVisibleRows(TheRange)
> > Dim CellToCount As Range
> > Dim RunningTotal As Long
> > RunningTotal = 0
> > For Each CellToCount In TheRange
> > If CellToCount.Row.Hidden = False Then
> > RunningTotal = RunningTotal + 1
> > End If
> > Next CellToCount
> > CountVisibleRows = RunningTotal
> > End Function
> >
> > but I'm getting a message to say that .row is an invalid
> > qualifier.
> > It's late in the day 9well, it is for me, anyway) and my brain
> > has entered
> > the twilight zone.
> >
> > Can anyone please help?
> >
> > Thanks in advance
> >
> > Pete
>
>
>

Thanks for this, Paul!

Pete

"[email protected]" wrote:

> Hi
> ..Rows instead of .Row
>
> regards
> Paul
>
>

> ...to display the number of visible rows with a
> range of cells.

Maybe another option:

Function CountVisibleRows(TheRange)
Application.Volatile
Dim r As Range '(R)ow
For Each r In TheRange.EntireRow
CountVisibleRows = CountVisibleRows - Not (r.Hidden)
Next r
End Function

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> This is what happens when you take a couple of months away from VBA...
>
> I'm trying to write a function to display the number of visible wows with
> a
> range of cells.
>
> Function CountVisibleRows(TheRange)
> Dim CellToCount As Range
> Dim RunningTotal As Long
> RunningTotal = 0
> For Each CellToCount In TheRange
> If CellToCount.Row.Hidden = False Then
> RunningTotal = RunningTotal + 1
> End If
> Next CellToCount
> CountVisibleRows = RunningTotal
> End Function
>
> but I'm getting a message to say that .row is an invalid qualifier.
> It's late in the day 9well, it is for me, anyway) and my brain has entered
> the twilight zone.
>
> Can anyone please help?
>
> Thanks in advance
>
> Pete

Thanks, Bob,

I need to book my memory in for a service!

regards

Pete



"Bob Phillips" wrote:

> Function CountVisibleRows(TheRange)
> Dim CellToCount As Range
> Dim RunningTotal As Long
> RunningTotal = 0
> For Each CellToCount In TheRange.Rows
> If Not CellToCount.EntireRow.Hidden Then
> RunningTotal = RunningTotal + 1
> End If
> Next CellToCount
> CountVisibleRows = RunningTotal
> End Function
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Peter Rooney" <[email protected]> wrote in message
> news:[email protected]...
> > This is what happens when you take a couple of months away from VBA...
> >
> > I'm trying to write a function to display the number of visible wows with
> a
> > range of cells.
> >
> > Function CountVisibleRows(TheRange)
> > Dim CellToCount As Range
> > Dim RunningTotal As Long
> > RunningTotal = 0
> > For Each CellToCount In TheRange
> > If CellToCount.Row.Hidden = False Then
> > RunningTotal = RunningTotal + 1
> > End If
> > Next CellToCount
> > CountVisibleRows = RunningTotal
> > End Function
> >
> > but I'm getting a message to say that .row is an invalid qualifier.
> > It's late in the day 9well, it is for me, anyway) and my brain has entered
> > the twilight zone.
> >
> > Can anyone please help?
> >
> > Thanks in advance
> >
> > Pete
>
>
>