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

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

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

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

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