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

Hi Eric

Dim rng As Range
Dim iCtr As Long

Set rng = Range("myTable")

iCtr = rng.Columns(1). SpecialCells(xlCellTypeVisible).Count

'Or, to exclude the header row:
iCtr = rng.Columns(1). SpecialCells(xlCellTypeVisible).Count -1

MsgBox iCtr



---
Regards,
Norman



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

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

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

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

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

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

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