autofilter row count

[Pierre]

Hi Experts,

I have the following code toset a filer on a sheet called "datadga"

Private Sub but_filter_click()
Dim choice As String
Dim column As Integer

If zoek_acc.Value <> "" Then
choice = zoek_acc.Value
column = 23
ElseIf zoek_status <> "" Then
choice = zoek_status.Value
column = 177
Else
MsgBox "make a choice first !", , "DGA-module"
Exit Sub
End If

With Sheets("datadga")
Range("A1:GZ10000").Select
Selection.AutoFilter Field:=column, Criteria1:=choice
Set filterrange = Cells.SpecialCells(xlCellTypeVisible).Cells(1)
Do
Set filterrange = filterrange.Offset(1, 0)
Loop While filterrange.EntireRow.Hidden = True
Call textboxes_fill
End With
End Sub

I would like to know how many rows meet the criteria but i cannot figure out
how to do that.
I tried a counter just befoor the "Loop..." line but it did not work
correctly, most of the time this counter gave too little rows return

Please help !
Thanks,
Pierre

 

[Pierre]

Hi experts,

solved the problem with extra code after Loop instruction;
dim filrange as range


Set filrange = ActiveSheet.AutoFilter.Range
MsgBox filrange.Columns(1). _
SpecialCells(xlCellTypeVisible).Count - 1 _
& " of " & filrange _
.Rows.Count - 1 & " Records"

Greetz,
Pierre

 

[Ron de Bruin]

Hi Pierre

You can use

MsgBox Application.WorksheetFunction.Subtotal(3, Range("A1:A10000")) - 1

-1 is for the header

You can also use 103 if your excel version is not to old
See subtotal help

 

[Dave Peterson]

After you filter the range, you can use something like:

with activesheet.autofilter.range
msgbox .columns(1).cells.specialcells(xlcelltypevisible).cells.count - 1
end with

(Subtracting 1 for the header)