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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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



message
news:D[email protected]...
 
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)
 
EntireRow!!!
It's scary how quickly these things drop oput of the grey matter.
Thanks a million, Chip - you're a lifesaver!

Pete
 
...to display the number of visible rows with a
range of cells.
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help me write a function please? 10
Referring to same cells in all worksheets 2
output standardization to to cell 3
Counting Bordered Cells 5
Selection.Replace doesn't update the cell value in macro! 1
Can you get a Merged Cell with Text Wrap to Autofit? 3
Excel VBA - counting cells by colors and names to it in two different rows. 0
Populating a Temporary Array 6

Back
Top