AutoFilter Row Count

[crisgruba]

Hi,

I am new to VBA and would like to know if there is a way to count the
number of rows based on the autofilter criteria?

sample code below
Columns("A").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="310"
Selection.AutoFilter Field:=4, Criteria1:=">0", Operator:=xlAnd

on my data, this displays 4 rows. I need to know the number of rows
so that I can create another procedure that loops through the result
set of the auto-filter.

i hope i make sense

thank you all in advance

Cris

 

[Bob Phillips]

Dim rng As Range

'get the number of rows in data
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
'extend range to column D
With rng.Resize(, 4)
.AutoFilter
.AutoFilter Field:=3, Criteria1:="310"
.AutoFilter Field:=4, Criteria1:=">0", Operator:=xlAnd
MsgBox "Num rows = " & rng.SpecialCells(xlCellTypeVisible).Count
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[crisgruba]

Dim rng As Range

'get the number of rows in data
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
'extend range to column D
With rng.Resize(, 4)
.AutoFilter
.AutoFilter Field:=3, Criteria1:="310"
.AutoFilter Field:=4, Criteria1:=">0", Operator:=xlAnd
MsgBox "Num rows = " & rng.SpecialCells(xlCellTypeVisible).Count
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi Bob,

Thanks for that. Although I have to do a count - 1 otherwise, it
includes my column headings. But other than that, works perfect.

Cris

 

[crisgruba]

Hi Bob,

Thanks for that. Although I have to do a count - 1 otherwise, it
includes my column headings. But other than that, works perfect.

Cris- Hide quoted text -

- Show quoted text -

Hi Bob,

Maybe you can help me again, if its not too much. I am having problems
getting the value of the cells that are on the autofilter result. I
can get their address though but it still not giving me the value that
i want. and if i do a rng.specialcells(xlCellTypeVisible).value, it
only gives me the "A1" value and it doesn't loop.

any help is greatly appreciated

thank you

Cris

 

[Bob Phillips]

Cris,

What exactly do you mean by the value of the cells, all of them?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)