Vba get exact number of filtered data in Excel

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


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 to concatenate strings in vba in Excel

2) The scenario manager in Excel VBA allows to explore various scenarios in a very easy way

3) How can I copy and rename a WorkSheet using VBA?

4) How can I enter information in multiple cells simultaneously?

5) Line break in vba message box in Excel

6) Here some explanations about the MSXML who stands for Microsoft XML core services

7) How can I get users to select a folder to save the output of my macro?

8) How can I list all files in a folder using VBA?

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

10) How to handle errors in VBA with the handling error methods


Here the previous and next chapter