Announcement

Collapse
No announcement yet.

Count rows in VBA when filter is on

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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
    I am not an expert. better solutions may be available. [email protected]$$$gmail.com

    Comment


    • #3
      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

      Comment

      Working...
      X