Here's the code I wound up implementing as a general purpose utility: Not
the one-liner I was looking for but it works and should be re-usable

Thanks,
Eric

'---------------------------------------------------------------------------------------
' Procedure : VisibleRowsInFilteredRange
' Purpose : To count the number of visible rows in the passed range, which
may have
' hidden rows and / or columns.
' Inputs : aRangeAddress : fully qualified address of the range
' : makeHeaderAdj : Optional, if True, the range has a header to
subtract.
' Outputs : Long : visible data rows in the range
' Precon(s) : None
' DateTime : 8/20/2005
' Author : EBF
'---------------------------------------------------------------------------------------
'
Public Function VisibleRowsInFilteredRange( _
ByVal aRangeAddress As String, Optional ByVal makeHeaderAdj As
Boolean = False) _
As Long

Dim rng As Excel.Range
Set rng = Application.Range(aRangeAddress)
' make sure we use a column that isn't hidden itself
Dim iCtr As Integer
For iCtr = 1 To rng.Columns.Count
If rng.Columns(iCtr).Hidden = False Then Exit For
Next iCtr

Dim lResult As Long
lResult = VisbleCells(rng.Columns(iCtr)).Count
If makeHeaderAdj Then lResult = lResult - 1

VisibleRowsInFilteredRange = lResult
Set rng = Nothing

End Function

'---------------------------------------------------------------------------------------
' Procedure : VisbleCells
' Purpose : To take the passed range and convert it into a range that
contains only visible cells.
' Inputs : aStartRange : the range that may or may not contain all
visible cells.
' Outputs : Range : the range converted to contain only visible
cells.
' Precon(s) : None
' DateTime : 8/1/2005
' Author : EBF
'---------------------------------------------------------------------------------------
'
Public Function VisbleCells(ByRef aStartRange As Excel.Range) As Excel.Range

On Error GoTo ErrorHandler

Const sPROCEDURE As String = "VisbleCells"
Set VisbleCells = aStartRange.SpecialCells(xlCellTypeVisible)

ExitHe
Exit Function

ErrorHandler:
If CentralErrorHandler(msMODULE, sPROCEDURE) Then
Stop
Resume
Else
Resume ExitHe
End If

End Function



"Norman Jones" wrote in message
...
Hi Dave,

I was showing how Eric could pick that visible column in code and not have
to
loop through the rows to get a count.

My apologies - You were indeed!

Crucially, missed the word 'visible' in:

Another way is to find the first visible column in that filtered range
and

and read no further.

But for your response, I would, therefore, have misssed a nice solution.

In partial penance, another possible solution which does not loop or
require a hardcoded column selection:

'========================
Public Sub Tester()
Dim rng As Range, iCtr As Long

On Error Resume Next
Set rng = ActiveSheet.AutoFilter.Range
On Error GoTo 0

If Not rng Is Nothing Then
iCtr = rng.SpecialCells(xlCellTypeVisible).Columns(1).Cel ls.Count - 1
Else
MsgBox "No AutoFilter range found!"
End If

MsgBox iCtr

End Sub

'<<========================

---
Regards,
Norman



"Dave Peterson" wrote in message
...
I was showing how Eric could pick that visible column in code and not have
to
loop through the rows to get a count.


Norman Jones wrote:

Hi Dave,

Another way is to find the first visible column in that filtered range
and
count
the number of cells in that column

Indeed so. That is why I suggested code for doing this. See my preceding
post.

See also Eric's response which, pertinently, included:

One thing your both saying is that you need to pick a column in the
range
first, which is complicated in my case by the fact that different
views of
the data may hide various columns. No big deal, but I was hoping there
might be one line of code that could count the rows in the range that
were
visible.

Thanks for the suggestions!

Hence my alternative suggestion.

---
Regards,
Norman

"Dave Peterson" wrote in message
...
Another way is to find the first visible column in that filtered range
and
count
the number of cells in that column:

Option Explicit
Sub CountVisibleRows2()

Dim wks As Worksheet
Dim rngV As Range
Dim rngF As Range
Dim FirstVisibleCell As Range

Set wks = ActiveSheet

If wks.AutoFilterMode Then
Set rngF = wks.AutoFilter.Range
Else
MsgBox "Please apply a filter"
Exit Sub
End If

Set FirstVisibleCell = Nothing
On Error Resume Next
Set FirstVisibleCell =
rngF.Cells.SpecialCells(xlCellTypeVisible).Cells(1 )
On Error GoTo 0

If FirstVisibleCell Is Nothing Then
MsgBox "unhide something in that filtered range!"
Exit Sub
End If

Set rngV = Intersect(rngF, FirstVisibleCell.EntireColumn) _
.Cells.SpecialCells(xlCellTypeVisible)

'subtract one for the header.
MsgBox "Visible rows = " & rngV.Cells.Count - 1

End Sub



Eric wrote:

One thing your both saying is that you need to pick a column in the
range
first, which is complicated in my case by the fact that different
views
of
the data may hide various columns. No big deal, but I was hoping
there
might
be one line of code that could count the rows in the range that were
visible.

Thanks for the suggestions!

"michdenis" wrote in message
...
Hi Eric,

In your worksheet : =SubTotal(3,A1:A200)-1

In vba :

NbVisibleLines = WorksheetFunction.Subtotal(3,range("A1:A200"))-1


Salutations!



"Eric" a écrit dans le message de news:
t...
I have a defined name dynamic table of data and I want to know the
number
of
visible rows after it is filtered.

Using
Application.Range("myTable").SpecialCells(xlVisibl eOnly).Count
will
give all cells, not rows. I can take the top of a column in the
table,
find
the last non-blank cell, and then use specialCells, but it seems
there
must
be something a bit more elegant.

Tanks Much,
Eric




--

Dave Peterson

--

Dave Peterson