conut number of times after filtering

[smiler44]

I want to know how many times something appears in a column in the visible cells after filtering.
for example book appears 12 times in a column, 3 are visible and 9 are hidden. I need the VBA code to return 3.

Below is what I have but it includes both hidden and visible instances

dim book as string

book = Application.WorksheetFunction.CountIfs(Sheets("All_Orders").Range("G1:G30000"), book)

other columns probably will have been filtered too. I have looked around the net but cannot get the examples to work. I'm using Excel 2010 and 2013

thank you in advance

smiler44

 

[Ranman256]

You dont count across columns. Access Data is queried vertically. you would count how many times the book is in a single column.
fields:
[clientID]
[Book]
[Date]

If you stored the book across many columns, then your database is designed wrong.

 

[smiler44]

Ranman256, counting how many times book appears in a single column is exactly what I want to do. Book will not appear in any other column. what is making it beyond me, is counting how many times it appears in that column via only the visible cells.

smiler44

 

[Rx_]

At the risk of repeating myself
Added an attachment using the simple SUBTOTAL with some baseball stats.

There are a couple of ways to do this.
This link is one method of counting the visible filtered UNIQUE times an item appears in a column.
Follow instructions for the Array Formula, it won't just copy/paste.
http://www.access-programmers.co.uk/forums/showthread.php?t=240342

Some variations:
http://www.access-programmers.co.uk/forums/showthread.php?t=249670

More Basically: I past my Header and data at Row 5
=SUBTOTAL(2,A6:A5000)
This SUBTOTAL formula counts the number of visible cells in the range. It actually omits the header row, I just don't trust it.
When entering the formula, look at the list of filtering criteria. There are choices.
This is key: The SUBTOTAL function only works in AutoFiltering and outlining.
Hiding rows manually will return incorrect values.

 

[smiler44]

RX thank you for your reply. alas for some reason when I try to open your excel file which looks like an xls file I get cannot open a php file message.


I looked at the first link but it's blown my mind I don't understand it. Do I need both the formula and code? I don't understand the code, could you talk me through it ?
This I understand ObjXL.Range("A4").Select but this, I have no idea
ObjXL.Selection.FormulaArray = _
"=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(R[2]C:R[9996]C,ROW(R[2]C:R[9996]C)-ROW(R[2]C),0,1)),MATCH

thank you smiler44

 

[smiler44]

I found this code at http://stackoverflow.com/questions/10849177/easiest-way-to-loop-through-a-filtered-list-with-vba

 Option Explicit

Sub Sample()
    Dim rRange As Range, filRange As Range, Rng as Range
    'Remove any filters
    ActiveSheet.AutoFilterMode = False

    '~~> Set your range
    Set rRange = Sheets("Sheet1").Range("A1:E10")

    With rRange
        '~~> Set your criteria and filter
        .AutoFilter Field:=1, Criteria1:="=1"

        '~~> Filter, offset(to exclude headers)
        Set filRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow

        Debug.Print filRange.Address

        For Each Rng In filRange
            '~~> Your Code
        Next
    End With

    'Remove any filters
    ActiveSheet.AutoFilterMode = False
End Sub

I had a play around with it and came up with

 Sub Sample()
    Dim rRange As Range, filRange As Range, Rng As Range
    
    Dim counter1 'As counter
    
    counter1 = 0
   
    '~~> Set your range
    Set rRange = Sheets("Sheet1").Range("A1:A10")
     With rRange
        '~~> Set your criteria and filter
        '.AutoFilter Field:=1, Criteria1:="good"
         '~~> Filter, offset(to exclude headers)
        Set filRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        For Each Rng In filRange
            '~~> Your Code
    Rng.Activate
    If ActiveCell.Text = "good" Then
    counter1 = counter1 + 1
    End If
        Next
    End With
 Range("A1").Select
MsgBox counter1
    
End Sub

 [/code
  
 so far this seems to do what I want but I need to test it some more but it's looking good
  
 smiler44

 

[Rx_]

ObjXL.Selection.FormulaArray = _
"=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(R[2]C:R[9996]C,ROW(R[2]C:R[9996]C)-ROW(R[2]C),0,1)),MATCH

This array formula can be put in by code OR it can be entered manually (remember the special keys to enter an Array formula).

I tried the download link, and it worked just fine for me. Wondering if your file association is off?

If you want to post a short version of your Excel with code, I will be glad to look at it.
More important, just let me know what the objectives are. I just might have another idea on how the objective could be accomplished.

 

[smiler44]

Hi RX,
sample code is above. I have a spread sheet with some 20,000 rows and some 80 columns. I only want some data and so using code create a new spread sheet copying across only the columns I want. This still gives me rows of information I do not need. In one of the columns is an ID of members of our team, the ID may appear more than once.
I want to know how many times each ID appears after I have filtered the column and hidden the ID's of people that are not on my team.

There is more work to do after this but I have that code already working, this is just an addition

smiler44