Excel Made Easy

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

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

Excel Made Easy was founded in 2002. Excel Made Easy is a service to people looking to learn Microsoft Excel or any other spreadsheet SW on the market.

We endeavor to provide the best service and support and to respond quickly to your questions.

Excel Made Easy is a proud sponsor of Dismantle-It.com the site that shows you the belly of your daily objects, Poupounette.com the site of cars loving cat and PowerPointMadeEasy.com the world top reference for PowerPoint Presentations questions and Pet Cemetery Online a site dedicated to your loving Pets.