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