Hi Eric,

Try:

Sub CountVisibleRows()
Dim sh As Worksheet
Dim rng As Range
Dim rw As Range

Set sh = ActiveSheet '<<===== CHANGE

i = 0

If sh.AutoFilterMode Then
Set rng = ActiveSheet.AutoFilter.Range
End If

On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rng Is Nothing Then
i = -1 'Allow for header row!
For Each rw In rng.Rows
i = i + 1
Next
End If

MsgBox "Visible rows = " & i

End Sub



---
Regards,
Norman



"Eric" wrote in message
nk.net...
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