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:D").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

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)

wrote in message
ups.com...
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:D").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

On Aug 2, 5:41 pm, "Bob Phillips" wrote:
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)

wrote in message

ups.com...

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:D").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

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

On Aug 3, 12:11 am, wrote:
On Aug 2, 5:41 pm, "Bob Phillips" wrote:





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)

wrote in message

oups.com...

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:D").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

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

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)

wrote in message
ups.com...
On Aug 3, 12:11 am, wrote:

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