Row count on the Filtered data

Question

I m using the below code to get the count of the filtered data rows in VBA  but while getting the count  it s giving the run time error showing       Object required      Could some please let me know what change s  is needed   Set rnData    UsedRange  With rnData      AutoFilter Field  327  Criteria1  Mid provarr q   1  2       Select      AutoFilter Field  328  Criteria1  Mid provarr q   3  7       Select      AutoFilter Field  330  Criteria1  Mid provarr q   10  2       Select      AutoFilter Field  331  Criteria1  Mid provarr q   12  2       Select       Rowz    AutoFilter Range SpecialCells xlCellTypeVisible  Rows count            End With

User · Answer

While I agree with the results given  they didn t work for me   If your Table has a name this will work   Public Sub GetCountOfResults WorkSheetName As String  TableName As String      Dim rnData As Range     Dim rngArea As Range     Dim lCount As Long         Set rnData   ThisWorkbook Worksheets WorkSheetName  ListObjects TableName  Range     With rnData         For Each rngArea In  SpecialCells xlCellTypeVisible  Areas             lCount   lCount   rngArea Rows Count         Next         MsgBox  Autofilter    amp  lCount - 1  amp    records      End With     Set rnData   Nothing     lCount   Empty       End Sub   This is modified to work with ListObjects from an original version I found here   http   www ozgrid com forum showthread php t 81858

User · Answer

Rowz   Application WorksheetFunction Subtotal 2  Range  quot A2 A quot   amp  Rows Rows Count  End xlUp  Row

User · Answer

Rowz   Application WorksheetFunction Subtotal 2  Range  A2 A   amp  Rows Rows Count  End xlUp  Row     This worked for me quite well

User · Answer

I know this an old thread  but I found out using the Subtotal method in VBA also accurately renders a count of the rows  The formula I found is in this article  and looks like this   Application WorksheetFunction Subtotal 2   Range  A2 A   amp   Rows  Rows Count  End xlUp  Row     I tested it and it came out accurately every time  rendering the correct number of visible rows in column A   Hopefully this will help some other wayfarer of the  Net like me

User · Answer

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

User · Answer

I would think that now you have the range for each of the row  you can easily manipulate that range with the offset row  column  action   What is the point of counting the records filtered  unless you need that count in a variable   So instead of  or as well as in the same block  write your code action to move each row to an empty hidden sheet and once all done  you can do any work you like from the transferred range data

User · Answer

I have found a way to do this that it requires 2 steps  but it works    to copy out a filtered selection into a different sheet   number of dinosaurs   WorksheetFunction Count Worksheets  Dinosaurs   Range  A2    A3000     With Worksheets  Dinosaurs        AutoFilterMode   False     With  Range   A 4  E    amp  number of dinosaurs           AutoFilter Field  2  Criteria1    teeth     change your criteria to whatever you like          SpecialCells xlCellTypeVisible  Copy Destination  Worksheets  Bad Dinosaurs   Range  A1       End With End With     then do a normal count on the secondary sheet    number of dinosaurs that eat humans   WorksheetFunction Count Worksheets  Bad Dinosaurs   Range  A2    A30000

User · Answer

Simply put this in your code   Application WorksheetFunction Subtotal 3  Range  A2 A500000      Make sure you apply the correct range  but just keep it to ONE column

[excel] Row count on the Filtered data

Examples related to excel

Examples related to vba