Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Count rows in VBA when filter is on

  1. #1
    Join Date
    8th March 2012
    Posts
    78

    Count rows in VBA when filter is on

    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.

    VB:
    Range("B1").End(xlDown).Row 
    
    
    The output is coming as 10 data instead of 2 data.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    13th May 2006
    Location
    India
    Posts
    1,013

    Re: Count rows in VBA when filter is on

    THE SHEET IS ALREADY AUTO FILTERED

    Then try this macro


    VB:
    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
    VB:
    ActiveSheet.AutoFilterMode = False 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    8th March 2010
    Posts
    1,651

    Re: Count rows in VBA when filter is on

    to count the number of filtered rows after the filtering:

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Return Count Of Visible Rows After Auto Filter
    By weihs in forum EXCEL HELP
    Replies: 6
    Last Post: September 17th, 2010, 00:55
  2. Count Of Records With Filter
    By dichs in forum EXCEL HELP
    Replies: 9
    Last Post: March 23rd, 2007, 19:28
  3. Count With Filter
    By dichs in forum EXCEL HELP
    Replies: 2
    Last Post: March 23rd, 2007, 19:04
  4. Auto Filter - Row Count
    By spike274 in forum EXCEL HELP
    Replies: 4
    Last Post: June 9th, 2004, 22:31
  5. Filter Row Count
    By RaVon in forum EXCEL HELP
    Replies: 1
    Last Post: March 29th, 2004, 16:08

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno