count only the visible rows in a data table

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(xlVisibleOnly).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" <[email protected]> wrote in message
news:[email protected]...
>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(xlVisibleOnly).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" <[email protected]> a écrit dans le message de news: [email protected]...
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(xlVisibleOnly).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" <[email protected]> wrote in message
news:[email protected]...
> Hi Eric,
>
> In your worksheet : =SubTotal(3,A1:A200)-1
>
> In vba :
>
> NbVisibleLines = WorksheetFunction.Subtotal(3,range("A1:A200"))-1
>
>
> Salutations!
>
>
>
> "Eric" <[email protected]> a écrit dans le message de news:
> [email protected]...
> 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(xlVisibleOnly).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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Eric,
>>
>> In your worksheet : =SubTotal(3,A1:A200)-1
>>
>> In vba :
>>
>> NbVisibleLines = WorksheetFunction.Subtotal(3,range("A1:A200"))-1
>>
>>
>> Salutations!
>>
>>
>>
>> "Eric" <[email protected]> a écrit dans le message de news:
>> [email protected]...
>> 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(xlVisibleOnly).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" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Eric,
> >
> > In your worksheet : =SubTotal(3,A1:A200)-1
> >
> > In vba :
> >
> > NbVisibleLines = WorksheetFunction.Subtotal(3,range("A1:A200"))-1
> >
> >
> > Salutations!
> >
> >
> >
> > "Eric" <[email protected]> a écrit dans le message de news:
> > [email protected]...
> > 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(xlVisibleOnly).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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi Eric,
>> >
>> > In your worksheet : =SubTotal(3,A1:A200)-1
>> >
>> > In vba :
>> >
>> > NbVisibleLines = WorksheetFunction.Subtotal(3,range("A1:A200"))-1
>> >
>> >
>> > Salutations!
>> >
>> >
>> >
>> > "Eric" <[email protected]> a écrit dans le message de news:
>> > [email protected]...
>> > 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(xlVisibleOnly).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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hi Eric,
> >> >
> >> > In your worksheet : =SubTotal(3,A1:A200)-1
> >> >
> >> > In vba :
> >> >
> >> > NbVisibleLines = WorksheetFunction.Subtotal(3,range("A1:A200"))-1
> >> >
> >> >
> >> > Salutations!
> >> >
> >> >
> >> >
> >> > "Eric" <[email protected]> a écrit dans le message de news:
> >> > [email protected]...
> >> > 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(xlVisibleOnly).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).Cells.Count - 1
Else
MsgBox "No AutoFilter range found!"
End If

MsgBox iCtr

End Sub

'<<========================

---
Regards,
Norman



"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Hi Eric,
>> >> >
>> >> > In your worksheet : =SubTotal(3,A1:A200)-1
>> >> >
>> >> > In vba :
>> >> >
>> >> > NbVisibleLines = WorksheetFunction.Subtotal(3,range("A1:A200"))-1
>> >> >
>> >> >
>> >> > Salutations!
>> >> >
>> >> >
>> >> >
>> >> > "Eric" <[email protected]> a écrit dans le message de news:
>> >> > [email protected]...
>> >> > 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(xlVisibleOnly).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)

ExitHere:
Exit Function

ErrorHandler:
If CentralErrorHandler(msMODULE, sPROCEDURE) Then
Stop
Resume
Else
Resume ExitHere:
End If

End Function



"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> 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).Cells.Count - 1
> Else
> MsgBox "No AutoFilter range found!"
> End If
>
> MsgBox iCtr
>
> End Sub
>
> '<<========================
>
> ---
> Regards,
> Norman
>
>
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > 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" <[email protected]> wrote in message
>>> >> news:[email protected]...
>>> >> > Hi Eric,
>>> >> >
>>> >> > In your worksheet : =SubTotal(3,A1:A200)-1
>>> >> >
>>> >> > In vba :
>>> >> >
>>> >> > NbVisibleLines = WorksheetFunction.Subtotal(3,range("A1:A200"))-1
>>> >> >
>>> >> >
>>> >> > Salutations!
>>> >> >
>>> >> >
>>> >> >
>>> >> > "Eric" <[email protected]> a écrit dans le message de news:
>>> >> > [email protected]...
>>> >> > 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(xlVisibleOnly).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).Cells.Count - 1
> Else
> MsgBox "No AutoFilter range found!"
> End If
>
> MsgBox iCtr
>
> End Sub
>
> '<<========================
>
> ---
> Regards,
> Norman
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > Hi Eric,
> >> >> >
> >> >> > In your worksheet : =SubTotal(3,A1:A200)-1
> >> >> >
> >> >> > In vba :
> >> >> >
> >> >> > NbVisibleLines = WorksheetFunction.Subtotal(3,range("A1:A200"))-1
> >> >> >
> >> >> >
> >> >> > Salutations!
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Eric" <[email protected]> a écrit dans le message de news:
> >> >> > [email protected]...
> >> >> > 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(xlVisibleOnly).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" <[email protected]> wrote in message
news:[email protected]...
>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).Cells.Count -
>> 1
>> Else
>> MsgBox "No AutoFilter range found!"
>> End If
>>
>> MsgBox iCtr
>>
>> End Sub
>>
>> '<<========================
>>
>> ---
>> Regards,
>> Norman
>>
>> "Dave Peterson" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > Hi Eric,
>> >> >> >
>> >> >> > In your worksheet : =SubTotal(3,A1:A200)-1
>> >> >> >
>> >> >> > In vba :
>> >> >> >
>> >> >> > NbVisibleLines = WorksheetFunction.Subtotal(3,range("A1:A200"))-1
>> >> >> >
>> >> >> >
>> >> >> > Salutations!
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > "Eric" <[email protected]> a écrit dans le message de
>> >> >> > news:
>> >> >> > [email protected]...
>> >> >> > 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(xlVisibleOnly).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