Couting with conditions

[GuillaumeJ]

hi everyone,

I want to create a short macro to count the number of cells in a column, based on a condition.
All the cells contains either "yes" or "no", and the options of counting are the number of yes, the number of no and all of them

Let's say I have all my values in the range A1:A100, my condition is registered in cell B1, knowing that the value can be "Yes", "No", "All"

My code is the following:

For i = 1 to 100
If range("A" &i).Value = Range("B1").value Then
count = count +1
Else
count = count
End if
Next i

Is there a way to put in the Range("B1").Value a kind of wildcard when the condition "All" is selected so then only my condition in B1 will change and not the macro itself ?

It may look a bit complex, but I want to count items with various conditions located in several cells, so I will keep this "If" adding conditions on anothers cells.

Any help will be great !

guillaume

 

[GuillaumeJ]

Back to business !

I tried your formula but the only result I can get is "1", and not the number of rows visible...

Do you have any idea ?

 

[Von Pookie]

Weird. I have no clue why it would be doing that.

I just did a test where I put a number in A1:A30, and hid some rows here and there. Right now, I have rows 1,2,17-20 visible and using

msgboxrange("A1:A30").SpecialCells(xlCellTypeVisible).Rows.Count

is returning a 2.

No clue why this is

Edit: However, if you only count in *one* column, I believe this should work. When I tried it using the above scenario, it returned the correct number of cells, which is also the correct # of rows:

Range("A1:A30").SpecialCells(xlCellTypeVisible).Count

 

[GuillaumeJ]

me neither !

but I found another solution finally, I copy the visible cells in a temp sheet and do a count on it...

thank you very much for your time in any case !