Count filtered rows

[Paul]

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.

 

[Rick Rothstein]

Just set YourRange below as needed...

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

 

[Gary''s Student]

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.

 

[Paul]

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'

 

[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.

 

[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.

 

[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.

 

[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.

 

[Rick Rothstein]

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).

 

[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

 

[Rick Rothstein]

Your interpretation of what Item(2) is not entirely correct either. The code
I posted worked fine, but only because I used a single column AutoFilter for
my testing. The number in the parentheses is does not refer to the column,
per se; rather, it refers to the second cell in the range with the order of
counting the cells running across the row first before dropping down to the
next lower row, and then across that row until its end before dropping down
to the row below it, and so on. So, in a single column range, Item(2) does,
in fact, refer to the second cell down because there is no second column
available in the first row for a cell there to be counted. If you have
multiple columns, then Item(2) would, in that case, as you pointed out,
refer to the first cell in the second column. Of course, that means my using
2 as a constant was a bad idea because I simply didn't think about two or
more columned AutoFilter when developing my code. A bad mistake on my part,
so thanks for catching it. However, I am glad I made that mistake so that
you could bring it to my attention. Why? Because in re-examining the
problem, I ended up realizing the required code is much simpler than I first
imagined it to be...the code for the main functionality turns out, in the
end, to basically be a one-liner.

Sub CountVisibleRows()
Dim R As Range, C As Long, OmitHeaderFromCount As Boolean
OmitHeaderFromCount = True
C = Worksheets("Sheet2").AutoFilter.Range.Columns(1). _
SpecialCells(xlVisible).Count + OmitHeaderFromCount
MsgBox "There are " & C & " visible AutoFilter'ed rows."
End Sub

Note that I added a Boolean variable named OmitHeaderFromCount to let the
programmer be able to set whether the header should be omitted from the
count or not. In my example above, I chose to omit it from the count by
setting the variable to True.