Hi all,

I have saw plenty of questions similiar to mine and i copied every single code snippet i could and tweeked it but i get the wrong value.

I have 2 worksheets(Raw data & Report). On the 'Report' worksheet which has summarized data i click an option button to filter the 'Raw data' worksheet. It works great! Now with that filter on say column 'BU' i have another column (AW) that has number values (Raw data worksheet).

I need to count those values in column 'AW'(e.g '=30' can have 20 entries) on the Raw Data worksheet and report those numbers on the Report worksheet.

I can get it to count all values =30 but it doesnt respect the filter and counts them all. I tried countif, subtotal, dcount, and looping statement.

Hi Mike,

Am Thu, 30 Jan 2014 12:46:17 -0800 (PST) schrieb Mike Ruiz:

I need to count those values in column 'AW'(e.g '=30' can have 20 entries) on the Raw Data worksheet and report those numbers on the Report worksheet.

I can get it to count all values =30 but it doesnt respect the filter and counts them all. I tried countif, subtotal, dcount, and looping statement.

try:

Sub Test()
Dim LRow As Long
Dim myCount As Long
Dim rngC As Range

With Sheets("Report")
LRow = .Cells(.Rows.Count, "BU").End(xlUp).Row
For Each rngC In .Range("AW2:AW" & LRow) _
.SpecialCells(xlCellTypeVisible)
If rngC = 30 Then
myCount = myCount + 1
End If
Next
End With
MsgBox myCount
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Thursday, January 30, 2014 3:57:45 PM UTC-5, Claus Busch wrote:
Hi Mike, Am Thu, 30 Jan 2014 12:46:17 -0800 (PST) schrieb Mike Ruiz: I need to count those values in column 'AW'(e.g '=30' can have 20 entries) on the Raw Data worksheet and report those numbers on the Report worksheet.. I can get it to count all values =30 but it doesnt respect the filter and counts them all. I tried countif, subtotal, dcount, and looping statement. try: Sub Test() Dim LRow As Long Dim myCount As Long Dim rngC As Range With Sheets("Report") LRow = .Cells(.Rows.Count, "BU").End(xlUp).Row For Each rngC In .Range("AW2:AW" & LRow) _ .SpecialCells(xlCellTypeVisible) If rngC = 30 Then myCount = myCount + 1 End If Next End With MsgBox myCount End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2



That worked!!!!!! Thanks