What is the Excel VBA to count number of rows in a filtered range?

kosenrufu
kosenrufu used Ask the Experts™
on
What is the Excel VBA to count number of rows in a filtered range?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hope this helps!

"When you have selected a range, it is sometimes useful to know how many rows or columns you have selected as this information can be used in your macros (for eg when you have reached the end, you will know it is time to stop the macros":

Sub Count()
myCount = Selection.Rows.Count    'Change Rows to Columns to count columns
MsgBox myCount
End Sub

http://www.angelfire.com/biz7/julian_s/julian/julians_macros.htm
VBA Developer
Commented:
Try this, see attached example.

Option Explicit

Sub VisibleCellsCount()
    Dim myRange As Range
    Dim lastRow As Long
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set myRange = Range("A2:A" & lastRow)
    MsgBox myRange.SpecialCells(xlCellTypeVisible).Count
    
End Sub

Open in new window

Book1.xls
How about for a range in question of D5 thru D1400:

msgbox WorksheetFunction.Subtotal(3, Range("d5:d1400"))

Of course, you could always use the Subtotal worksheet function right in the worksheet.  You don't really need VBA.  The Subtotal function can also give you average, min, max, count, counta, sum of the visible rows in a filtered range.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial