vba get exact number of filtered data in Excel

Q173. In Excel, how can I get row count of filtered data?

For example, I want to filter records pertaining to only Barbara and do some processing. For doing processing of the filtered records, I need to know the number of filtered records.

excel vba get exact number of filtered data

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub CountFilteredData()
  3. Dim sName As String
  4. Dim rngArea As Range
  5. Dim lngRecordCount As Long
  6. '''
  7. sName = "Barbara"
  8. '''
  9. 'Filter rows based on Name which is Field 2 (Col AQ).
  10. ActiveSheet.Range("AP4:AR4").AutoFilter
  11. ActiveSheet.Range("AP4:AR14").AutoFilter Field:=2, Criteria1:=sName
  12. '''
  13. lngRecordCount = 0
  14. For Each rngArea In ActiveSheet.Range("AP4:AR14").SpecialCells(xlCellTypeVisible).Areas
  15. lngRecordCount = lngRecordCount + 1
  16. Next
  17. ''
  18. 'Remove filter that was applied.
  19. ActiveSheet.AutoFilterMode = False
  20. End Sub

Description:

a) Line 13 - RecordCount is the variable corresponding to number of filtered records.

b) Line 14, 15,16 - Loop through the filtered data and count number of rows.

 

You can find similar Excel Questions and Answer hereunder

1) How can I dynamically add a hyperlink using VBA?

2) How can I avoid updates to cell values during macro execution?

3) Concatenate number with text but keeping in number format in Excel

4) How do I enter a formula in a cell using VBA (using Absolute Reference)?

5) How can I get the information about my current operating environment (OS version, Excel version, Current Directory)

6) Determine if hyperlinks are valid in Excel

7) Vba code to password protect workbook in Excel

8) How do i put double quotes in a string in vba in Excel

9) I have to retrieve data from a specific column in a Table. In Excel, how can I do that?

10) How to concatenate strings in vba in Excel

11) Millions thousands custom number formatting in Excel

12) How can I hide a sheet completely from users (the sheet should not even appear in Unhide dialog box)?

13) How can I extract file name from a full path including folder path and file name?

14) How can I identify all cells with Data Validation in my WorkSheet?

15) How can I dynamically add series to an existing chart using VBA?