AutoFilter Row Count
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
|