Hi Dave

I think you got bit by a data problem when you were testing.

Yes. I unadvisedly used a condition in a sequential list and thus missed
the problems associated with multiple areas.

I will return to my previous suggestions or use the entire column intersect
method, which I first saw (and liked) in a post from Dana DeLouis.

Thank you.

---
Regards,
Norman



"Dave Peterson" wrote in message
...
I think you got bit by a data problem when you were testing.

If the visible rows were non-contiguous, then I only got a count of the
first
area.





Norman Jones wrote:

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

--

Dave Peterson