Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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.

    Code:
    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,030

    Re: Count rows in VBA when filter is on

    THE SHEET IS ALREADY AUTO FILTERED

    Then try this macro


    Code:
    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
    Code:
    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:

    Code:
    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, 01:55
  2. Count Of Records With Filter
    By dichs in forum EXCEL HELP
    Replies: 9
    Last Post: March 23rd, 2007, 20:28
  3. Count With Filter
    By dichs in forum EXCEL HELP
    Replies: 2
    Last Post: March 23rd, 2007, 20:04
  4. Auto Filter - Row Count
    By spike274 in forum EXCEL HELP
    Replies: 4
    Last Post: June 9th, 2004, 23:31
  5. Filter Row Count
    By RaVon in forum EXCEL HELP
    Replies: 1
    Last Post: March 29th, 2004, 17: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