Count rows in VBA when filter is on

[Rajith]

I have filtered some data in the excel sheet and i tried to count those filtered data using VBA. For instance, if the number of row data is 10 and after filtering i got only 2 data. When i am filtering using the below code.

Range("B1").End(xlDown).Row 

The output is coming as 10 data instead of 2 data.

[venkat1926]

THE SHEET IS ALREADY AUTO FILTERED

Then try this macro

Sub filt_rows() 
    Dim filt As Range, j As Integer, totalrows As Integer 
    Set filt = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible) 
    MsgBox filt.Areas.Count 
    totalrows = 0 
    For j = 1 To filt.Areas.Count 
        totalrows = totalrows + filt.Areas(1).Rows.Count 
    Next j 
    MsgBox totalrows 
    ActiveSheet.AutoFilterMode = False 
End Sub 

the total rows include header row also

the code given below is an optional code. if you do not want it you can removed from the macro

ActiveSheet.AutoFilterMode = False 

[snb]

to count the number of filtered rows after the filtering:

Sub snb() 
    msgbox cells(1).currentregion.columns(1).specialcells(12).count-1 " rows filtered" 
End Sub