Counting in a variable range
 

Hi and thank you in advance.

I'm trying to count the word "Standard" in a series of ranges. Say A10-A15,
then maybe A40-A43...it keeps changing from 3 rows upto 10 rows.

Is there anyway to count in a range when the range keeps changing in the
amount of rows. Like it won't ever be only 5 rows, it may be 3 rows, 6 rows,
9 rows and then 2 rows.

Any help is greatly appreciated.

Thank you again.

Tim

Counting in a variable range
 

Select the range you wish to count in

run this procedure to display a messagebox with the result

:======================================

Sub CountInSelection()

MsgBox Application.WorksheetFunction.CountIf(Selection, "Standard")

End Sub

:======================================

else declare a variable to th result and use as you wish. Let me know
if you need any more help for you use.

http://www.excel-ant.co.uk

Counting in a variable range
 

Piece together the sections and then count within the union:

Sub example()
Dim r, rr As Range
Dim countit As Integer
Set r = Union(Range("A10:A15"), Range("A40:A43"))
countit = 0
For Each rr In r
If rr.Value = "Standard" Then
countit = countit + 1
End If
Next
MsgBox (countit)
End Sub

Alternatively, you can select the areas in the worksheet and then:

For Each rr in Selection


--
Gary's Student


"Tim" wrote:

Hi and thank you in advance.

I'm trying to count the word "Standard" in a series of ranges. Say A10-A15,
then maybe A40-A43...it keeps changing from 3 rows upto 10 rows.

Is there anyway to count in a range when the range keeps changing in the
amount of rows. Like it won't ever be only 5 rows, it may be 3 rows, 6 rows,
9 rows and then 2 rows.

Any help is greatly appreciated.

Thank you again.

Tim

Counting in a variable range
 

Thank you both for your response...I am working with your code now, it has
given me what I needed!

Thank you very very much! You guys are awsome!

"Tim" wrote:

Hi and thank you in advance.

I'm trying to count the word "Standard" in a series of ranges. Say A10-A15,
then maybe A40-A43...it keeps changing from 3 rows upto 10 rows.

Is there anyway to count in a range when the range keeps changing in the
amount of rows. Like it won't ever be only 5 rows, it may be 3 rows, 6 rows,
9 rows and then 2 rows.

Any help is greatly appreciated.

Thank you again.

Tim