How to count the cells in range selection

[VBABEGINER]

Hi,

Anyone have an idea about following.

col C
c7 van
c8 van
c9 van
c10 van
c11 van
c12 peninsula
c13 peninsula
c14 peninsula
c15 brazil
c16 brazil
c17 brazil
c18 brazil

As soon as the value change in c12, from c7 to c11 value need count.

How this value can i get counting of cells of same data?

I am using following code, if any one having idea kindly provide great solution.

Public Function group()

Dim i As Long, j As Long
'Dim p As Integer

i = Range("C" & Rows.Count).End(xlUp).Row
For j = 7 To i
If Cells(j, 3) <> Cells(j + 1, 3) Then
p = Cells(j, 11)
Range(Cells(j, 3), Cells(p, 11)).Select <-- ' i knw dis is nt proper
Selection.Rows.group
'Else
' lngRow = lngRow + 1
End If
Next j
End Function

 

[VBABEGINER]

Is it something to hard to explain guys? or the query is tuff?

since not getting any responce. Please guys this will make my report 100%

 

[niceguy_jay]

try this...the count is posted in the next cell when the value changes

Public Function group()

Dim j, p As Integer
i = Range("C" & Rows.Count).End(xlUp).Row
j = 7
Cells(j, 3).Select
Do
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
p = p + 1
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(0, 1).Value = p + 1
p = 0
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Row > i
End Function

 

[Weaver]

no need for all that 'select' malarky

Public Function group()
    Dim j, p As Integer
    i = Range("C" & Rows.Count).End(xlUp).Row
    j = 7
    For j = 7 To i
        If Cells(j, 3).Value = Cells(j + 1, 3).Value Then
            p = p + 1
        Else
            Cells(j, 4).Value = p + 1
            p = 0
        End If
    Next j
End Function

 

[Rick Rothstein]

Do you have to use a macro for this? Here is a formula that does what I think you are asking for (put it in D7 and copy it down)...

=IF(C7<>C6,COUNTIF(C$7:C$99,C7),"")

Change the 99 in the C$99 cell reference to a number larger than the highest row number you expect to have data in.