Row count on the Filtered data

by

If you try to count the number of rows in the already autofiltered range like this:

Rowz = rnData.SpecialCells(xlCellTypeVisible).Rows.Count

It will only count the number of rows in the first contiguous visible area of the autofiltered range. E.g. if the autofilter range is rows 1 through 10 and rows 3, 5, 6, 7, and 9 are filtered, four rows are visible (rows 2, 4, 8, and 10), but it would return 2 because the first contiguous visible range is rows 1 (the header row) and 2.

A more accurate alternative is this (assuming that ws contains the worksheet with the filtered data):

Rowz = ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1

We have to subtract 1 to remove the header row. We need to include the header row in our counted range because SpecialCells will throw an error if no cells are found, which we want to avoid.

The Cells property will give you an accurate count even if the Range has multiple Areas, unlike the Rows property. So we just take the first column of the autofilter range and count the number of visible cells.

More Related Contents:

  1. SumIf using Excel VBA
  2. How can I call a macro which contains a loop but only run one iteration of that loop?
  3. Test or check if sheet exists
  4. How can I find last row that contains data in a specific column?
  5. Copy from one workbook and paste into another
  6. How to copy Outlook mail message into excel using VBA or Macros
  7. Open Excel file for reading with VBA without display
  8. For Loop not fully cycling in excel VBA [duplicate]
  9. Unique Random Numbers using VBA
  10. Split cell values into multiple rows and keep other data
  11. Save each sheet in a workbook to separate CSV files
  12. Difference between ‘on error goto 0’ and ‘on error goto -1’ — VBA
  13. Is VBA an OOP language, and does it support polymorphism?
  14. Export each sheet to a separate csv file
  15. What are .NumberFormat Options In Excel VBA?
  16. Excel: How to check if a cell is empty with VBA? [duplicate]
  17. Detect if range is empty
  18. Checking if a worksheet-based checkbox is checked
  19. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  20. Extract Data from Word Document to an Excel SpreadSheet
  21. Excel macro -Split comma separated entries to new rows [duplicate]
  22. VBA equivalent to Excel’s mod function
  23. Get text from clipboard using GetText – avoid error on empty clipboard
  24. Pointers needed for speeding up nested loop macro in VBA
  25. Display milliseconds in Excel
  26. VBA editor auto-deletes spaces at the ends of lines
  27. ‘1004’: “The sort reference is not valid.”
  28. Select entire column in table using Excel VBA
  29. Reducing WithEvent declarations and subs with VBA and ActiveX
  30. Delete all data rows from an Excel table (apart from the first)

Leave a Comment