Is there a way of calculating the number of visible rows when filtered ?

I need to find a way that DOESN'T require a specific column to be fully
populated (so SUBTOTAL is not the answer). This is because the column on
which filter is being applied is user definable and therefore has the
potential for blanks.

Just set YourRange below as needed...

On Error Resume Next
VisibleRows = YourRange.SpecialCells(xlCellTypeVisible).Count

--
Rick (MVP - Excel)


"Paul" <(E-Mail Removed)> wrote in message
news:82979C53-0537-4843-B768-(E-Mail Removed)...
> Is there a way of calculating the number of visible rows when filtered ?
>
> I need to find a way that DOESN'T require a specific column to be fully
> populated (so SUBTOTAL is not the answer). This is because the column on
> which filter is being applied is user definable and therefore has the
> potential for blanks.

Here is an example for AutoFilter:

Sub FilterVisible()
Dim r1 As Range, r2 As Range
Set r1 = ActiveSheet.AutoFilter.Range
Set r2 = ActiveSheet.Cells.SpecialCells(xlVisible)
n = Intersect(r1, r2).Rows.Count - 1
MsgBox (n)
End Sub

The -1 is optional. Use it to not count the "header" row.
--
Gary''s Student - gsnu200908


"Paul" wrote:

> Is there a way of calculating the number of visible rows when filtered ?
>
> I need to find a way that DOESN'T require a specific column to be fully
> populated (so SUBTOTAL is not the answer). This is because the column on
> which filter is being applied is user definable and therefore has the
> potential for blanks.

Gary

Thanks for your quick response.

I've copied the code and run on a filtered range. It keeps giving me the
answer '1'

Just in case, I'm using Excel 2003.
[Note : I seem to have issues with Intersect before and I don't know whether
it's me or whether I need something 'switched on'

"Gary''s Student" wrote:

> Here is an example for AutoFilter:
>
> Sub FilterVisible()
> Dim r1 As Range, r2 As Range
> Set r1 = ActiveSheet.AutoFilter.Range
> Set r2 = ActiveSheet.Cells.SpecialCells(xlVisible)
> n = Intersect(r1, r2).Rows.Count - 1
> MsgBox (n)
> End Sub
>
> The -1 is optional. Use it to not count the "header" row.
> --
> Gary''s Student - gsnu200908
>
>
> "Paul" wrote:
>
> > Is there a way of calculating the number of visible rows when filtered ?
> >
> > I need to find a way that DOESN'T require a specific column to be fully
> > populated (so SUBTOTAL is not the answer). This is because the column on
> > which filter is being applied is user definable and therefore has the
> > potential for blanks.

Hello Paul and Gary,

Under test Gary's code fails. With filtered data and SpecialCells(xlVisible)
you cannot count rows because the Rows.Count only returns a number up to the
end of the first visible block.

However, you can count the cells. Therefore set a range to one column of the
visible data and count the cells in that range as follows.

Sub CountVisibleRows()
Dim rngFiltColumn As Range

'Edit "Sheet1" to your sheet name
With Sheets("Sheet1").AutoFilter.Range
Set rngFiltColumn = .Offset(1, 0) _
.Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlVisible)
End With

MsgBox rngFiltColumn.Cells.Count

End Sub

An explanation of the setting of rngFiltColumn.
Offset moves it down one row off the column headers. However, this then
includes an additional row at the bottom so Resize reduces by one row and the
column parameter in Resize set to 1 only returns one column.
SpecialCells(xlVisible) is self explanatory.

As some added info. You cannot use for i = 1 to Rows.count with the filtered
data. However, you can use for each cell in the column range as follows.

Dim c As Range
Dim i As Long
For Each c In rngFiltColumn
'Reference adjacent columns with offset.
For i = 0 To 5
MsgBox c.Offset(0, i).Value
Next i
Next c

Note for ease of programming you can use Offset(0, 0) which effectively does
not offset. This is used in the first iteration of the above.

Hope this helps.

--
Regards,

OssieMac

Hello Paul and Gary,

Under test Gary's code fails. With filtered data and SpecialCells(xlVisible)
you cannot count rows because the Rows.Count only returns a number up to the
end of the first visible block.

However, you can count the cells. Therefore set a range to one column of the
visible data and count the cells in that range as follows.

Sub CountVisibleRows()
Dim rngFiltColumn As Range

'Edit "Sheet1" to your sheet name
With Sheets("Sheet1").AutoFilter.Range
Set rngFiltColumn = .Offset(1, 0) _
.Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlVisible)
End With

MsgBox rngFiltColumn.Cells.Count

End Sub

An explanation of the setting of rngFiltColumn.
Offset moves it down one row off the column headers. However, this then
includes an additional row at the bottom so Resize reduces by one row and the
column parameter in Resize set to 1 only returns one column.
SpecialCells(xlVisible) is self explanatory.

As some added info. You cannot use for i = 1 to Rows.count with the filtered
data. However, you can use for each cell in the column range as follows.

Dim c As Range
Dim i As Long
For Each c In rngFiltColumn
'Reference adjacent columns with offset.
For i = 0 To 5
MsgBox c.Offset(0, i).Value
Next i
Next c

Note for ease of programming you can use Offset(0, 0) which effectively does
not offset. This is used in the first iteration of the above.

Hope this helps.

--
Regards,

OssieMac

Hello Paul and Gary's Student,

Having trouble positing this so my apologies if you get it twice.

With filtered data and SpecialCells(xlVisible) you cannot count rows because
the Rows.Count only returns a number up to the end of the first visible block.

However, you can count the cells. Therefore set a range to one column of the
visible data and count the cells in that range as follows.

Sub CountVisibleRows()
Dim rngFiltColumn As Range

'Edit "Sheet1" to your sheet name
With Sheets("Sheet1").AutoFilter.Range
Set rngFiltColumn = .Offset(1, 0) _
.Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlVisible)
End With

MsgBox rngFiltColumn.Cells.Count

End Sub

An explanation of the setting of rngFiltColumn.
Offset moves it down one row off the column headers. However, this then
includes an additional row at the bottom so Resize reduces by one row and the
column parameter in Resize set to 1 only returns one column.
SpecialCells(xlVisible) is self explanatory.

As some added info. You cannot use for i = 1 to Rows.count with the filtered
data. However, you can use for each cell in the column range as follows.

Dim c As Range
Dim i As Long
For Each c In rngFiltColumn
'Reference adjacent columns with offset.
For i = 0 To 5
MsgBox c.Offset(0, i).Value
Next i
Next c

Note for ease of programming you can use Offset(0, 0) which effectively does
not offset. This is used in the first iteration of the above.

Hope this helps.

--
Regards,

OssieMac

Hello Paul and Gary's Student,

With filtered data and SpecialCells(xlVisible) you cannot count rows because
the Rows.Count only returns a number up to the end of the first visible block.

However, you can count the cells. Therefore set a range to one column of the
visible data and count the cells in that range as follows.

Sub CountVisibleRows()
Dim rngFiltColumn As Range

'Edit "Sheet1" to your sheet name
With Sheets("Sheet1").AutoFilter.Range
Set rngFiltColumn = .Offset(1, 0) _
.Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlVisible)
End With

MsgBox rngFiltColumn.Cells.Count

End Sub

An explanation of the setting of rngFiltColumn.
Offset moves it down one row off the column headers. However, this then
includes an additional row at the bottom so Resize reduces by one row and the
column parameter in Resize set to 1 only returns one column.
SpecialCells(xlVisible) is self explanatory.

As some added info. You cannot use for i = 1 to Rows.count with the filtered
data. However, you can use for each cell in the column range as follows.

Dim c As Range
Dim i As Long
For Each c In rngFiltColumn
'Reference adjacent columns with offset.
For i = 0 To 5
MsgBox c.Offset(0, i).Value
Next i
Next c

Note for ease of programming you can use Offset(0, 0) which effectively does
not offset. This is used in the first iteration of the above.

Hope this helps.

--
Regards,

OssieMac

Here is another way to write it...

Sub CountVisibleRows()
Dim R As Range, C As Long
With Worksheets("Sheet2").AutoFilter.Range
C = Range(.Item(2), .Item(.Count)).SpecialCells(xlVisible).Count
End With
MsgBox "There are " & C & " visible AutoFilter'ed rows."
End Sub

Using the Item(2) makes the count skip the header row; if you want to count
the header row, then change it to Item(1).

--
Rick (MVP - Excel)


"OssieMac" <(E-Mail Removed)> wrote in message
news:5E7C120A-31E7-4CA2-AB8F-(E-Mail Removed)...
> Hello Paul and Gary's Student,
>
> With filtered data and SpecialCells(xlVisible) you cannot count rows
> because
> the Rows.Count only returns a number up to the end of the first visible
> block.
>
> However, you can count the cells. Therefore set a range to one column of
> the
> visible data and count the cells in that range as follows.
>
> Sub CountVisibleRows()
> Dim rngFiltColumn As Range
>
> 'Edit "Sheet1" to your sheet name
> With Sheets("Sheet1").AutoFilter.Range
> Set rngFiltColumn = .Offset(1, 0) _
> .Resize(.Rows.Count - 1, 1) _
> .SpecialCells(xlVisible)
> End With
>
> MsgBox rngFiltColumn.Cells.Count
>
> End Sub
>
> An explanation of the setting of rngFiltColumn.
> Offset moves it down one row off the column headers. However, this then
> includes an additional row at the bottom so Resize reduces by one row and
> the
> column parameter in Resize set to 1 only returns one column.
> SpecialCells(xlVisible) is self explanatory.
>
> As some added info. You cannot use for i = 1 to Rows.count with the
> filtered
> data. However, you can use for each cell in the column range as follows.
>
> Dim c As Range
> Dim i As Long
> For Each c In rngFiltColumn
> 'Reference adjacent columns with offset.
> For i = 0 To 5
> MsgBox c.Offset(0, i).Value
> Next i
> Next c
>
> Note for ease of programming you can use Offset(0, 0) which effectively
> does
> not offset. This is used in the first iteration of the above.
>
> Hope this helps.
>
> --
> Regards,
>
> OssieMac
>
>
>

Hi Rick,

I always like seeing alternative methods. However, your code needed a little
tweeking.

I believe you have the wrong interpretation of Item(2). Item(2) refers to
the 2nd column and your code counts all visible cells, including column
headers, to the end of the autofiltered range.

Changing the 2 to Columns.Count will count all visible cells from the start
of the last column to the end of the autofiltered range and includes column
headers. This is demonstrated in the following example that displays the
ranges being counted as well as the row count.

Sub CountVisibleRows2()

Dim R As Range, C As Long
With Worksheets("Sheet1").AutoFilter.Range
MsgBox Range(.Item(.Columns.Count), _
.Item(.Count)).SpecialCells(xlVisible).Address

C = Range(.Item(.Columns.Count), _
.Item(.Count)).SpecialCells(xlVisible).Count
End With

'Subtract the column header
MsgBox "There are " & C - 1 & " visible AutoFilter'ed rows."

End Sub


--
Regards,

OssieMac