How many rows?

[John]

People,
I have an Excel worksheet and I have applied an autofilter to a
particular column. The result is a filtered set of rows. Using VBA, what
is the easiest way to,
a) tell if any rows with data are in the filtered set (i.e. did the
filter find anything that met the criteria)?
b) get a count of how many rows with data, are shown in the filtered set?
c) loop through rows/columns/cells in the filtered set?

A bit of background. I write VBA for Project and in Project all one
needs to do to determine if a filter shows anything is to check for an
activeselection > 0. Looping is very simple - just loop through all
tasks/resources in the activeselection.

John
Project MVP

 

[Guest]

use the subtotal worksheet function

if application.Subtotal(3,Range("A2:A500"))


where Column A holds numbers. Look at help for Subtotal in Excel itself for
all options.

Subtotal only counts the visible cells in the filtered range.

Dim rng as Range
On Error Resume Next
set rng = Range("A2:A500").SpecialCells(xlVisible)
On Error goto 0
if not rng is nothing then
for each cell in rng

 

[John]

use the subtotal worksheet function

if application.Subtotal(3,Range("A2:A500"))


where Column A holds numbers. Look at help for Subtotal in Excel itself for
all options.

Subtotal only counts the visible cells in the filtered range.

Dim rng as Range
On Error Resume Next
set rng = Range("A2:A500").SpecialCells(xlVisible)
On Error goto 0
if not rng is nothing then
for each cell in rng

Tom,
Thanks for the quick reply. The process for doing what I want is
obviously less elegant than doing the same thing in Project. I guess
that is because Excel always has 256 columns and 65K rows whether there
is data in them or not. In Project the task collection object, for
example, is only as big as the number of tasks entered by the user.

I took a look at the Subtotal worksheet function description in the
object browser. That has got to be one of the least comprehensible
descriptions I've read. At the Worksheet Object level, there is
absolutely no explanation of what the arguments are for and the example
only covers the Range Object level, which does have a decent description
of the arguments. I should complain, I have found many places in the
Project object browser descriptions that are either flat out wrong,
misleading or confusing.

The SpecialCells Method for the Range object looks more promising. I
doubt I would have found that obscure method during my own search of
Excel's object model. Basically what I am doing is replicating the
grouping function available in MS Project. The overall macro I'm writing
exports selected data from Project to Excel. One subroutine finds rows
with columns containing certain data. It then adds a row above that
group for a group label.

Unfortunately either I'm not understanding something or the SpecialCells
Method doesn't work as advertised. My worksheet consists of 67 rows by 9
columns (i.e. the UsedRange). The following code snippet is part of the
above mentioned subroutine (s is my worksheet object and SrtCol is a
variable defining a particular column). In this case, I'm looking for no
data in the SrtCol. In other cases I'm looking for non-blank data so I
use the Find Method after the autofilter and that works fine.

s.UsedRange.AutoFilter Field:=SrtCol, Criteria1:=""
On Error Resume Next
Set Rng = s.UsedRange.SpecialCells(xlCellTypeVisible)

I stepped through the code. The autofilter finds 6 rows that meet the
criteria. That works fine. However when I use the Count property on the
rows of the Rng object, it only counts one row. That's not right, or at
least, it is not what I need. I need it to set the Rng object as the 6
found rows.

Your further insight would be appreciated.

John

 

[Guest]

Hrllo John

Look at help for Subtotal in Excel itself for all options.

I didn't say the object browser or VBA help. Subtotal is a worksheet
function being used in VBA. And note this is the Worksheet Function
Subtotal, not the method Subtotal which is found under the Data menu.

Subtotal(3,Range) counts numbers in the range, so the column would need to
contain numbers. Subtotal(3,range) counts non-empty cells, so the column
would need to not be empty. If you want to get an accurate count.

Excel has a group and outline capability which can be found under the Data
menu in Excel itself. I don't know if that adds anything to what you are
trying to do.

Note that all my examples in the previous post were performed on a single
column. In this way, rng.count gives the number of visible rows. It also
started in A2 so that the header row was excluded in the count.


s.UsedRange.AutoFilter Field:=SrtCol, Criteria1:=""
produces an Autofilter which displays only the rows that have blanks in
column SrtCol, so subtotal applied to that column would return nothing - see
the previous discussion

from the immediate window:
\
SrtCol = 2
set s = Activesheet
s.UsedRange.AutoFilter Field:=SrtCol, Criteria1:=""
Set Rng = s.UsedRange.SpecialCells(xlCellTypeVisible)
? Rng.count
42

so I got a count, but it doesn't mean much because it is all visible cells
in the usedrange.

? intersect(rng,columns(1)).Count
7

so looking at a single column tells me 7 rows are visible (including the
header row).

 

[Guest]

Correction to my last post:

Subtotal(2,range) is for Numbers - equivalent to Count, but counting only
rows that are visible when a filter is applied

Subtotal(3,range) is for non empty cells - equivalent to CountA, but
counting only rows that are visible when a filter is applied

But again, consult the EXCEL help (spreadsheet has focus when you choose
help) on Subtotal for details.

 

[John]

Hrllo John
I didn't say the object browser or VBA help. Subtotal is a worksheet
function being used in VBA. And note this is the Worksheet Function
Subtotal, not the method Subtotal which is found under the Data menu.

Subtotal(3,Range) counts numbers in the range, so the column would need to
contain numbers. Subtotal(3,range) counts non-empty cells, so the column
would need to not be empty. If you want to get an accurate count.

Excel has a group and outline capability which can be found under the Data
menu in Excel itself. I don't know if that adds anything to what you are
trying to do.

Note that all my examples in the previous post were performed on a single
column. In this way, rng.count gives the number of visible rows. It also
started in A2 so that the header row was excluded in the count.


s.UsedRange.AutoFilter Field:=SrtCol, Criteria1:=""
produces an Autofilter which displays only the rows that have blanks in
column SrtCol, so subtotal applied to that column would return nothing - see
the previous discussion

from the immediate window:
\
SrtCol = 2
set s = Activesheet
s.UsedRange.AutoFilter Field:=SrtCol, Criteria1:=""
Set Rng = s.UsedRange.SpecialCells(xlCellTypeVisible)
? Rng.count
42

so I got a count, but it doesn't mean much because it is all visible cells
in the usedrange.

? intersect(rng,columns(1)).Count
7

so looking at a single column tells me 7 rows are visible (including the
header row).

Tom,
First of all, no the group and outline functions in Excel will NOT do
what I need.

I guess I must have a different picture of what "visible" means. When my
filter is applied to a file with 67 rows, it produces a selected set of
6 rows. Using the Set Rng = s.usedrange.specialcells(xlCellTypeVisible)
should then give a row count of 6 since those are the only "visible"
rows in the used range. Obviously my concept of "visible" is wrong. Oh
well.

Let me get back to my original question. Yes I did ask how to get a
count of rows in a filter but I also asked how to loop through only the
rows found by the filter. The specific thing I need is to activate the
first row in the filtered set (not the filter header row), so that I can
insert my group label row ahead of it.

John

 

[John]

Tom,
OK, I finally figured it out but it was like pulling teeth. I didn't
like the idea of having to arbitrarily set a range that started with the
first non-header row of the filtered set and including enough rows to
cover the used range of my file, so I ended up using the following
sequence of code.

s.UsedRange.AutoFilter Field:=5, Criteria1:=""
Set Rng =
s.range(s.cells(2,1),s.cells(s.usedrange.rows.count,1)).SpecialCells(xlCe
llTypeVisible)
Rng.rows(1).activate

That sequence gives me the datum for adding my group header line.

Thank you for your assistance.

John