counta question

[chris808]

Aloha,
Im sure this will be an easy one for you guys here, but it sure hasnt been for me. I promise as simple as this sounds, Ive looked through old posts and help files but cant quite get it. I have a database that is 1 column wide and about 5000 rows long. Its set up like this:

ID 8497
item
item
item
item

ID 5542
item
item
item
item
item

and on and on and on..... the number if items is usually between 100 and 600. I need to count the number of items under each ID and have been basically just been using counta and manually putting the range in for each ID. There are 2 blank rows between each ID. I do this weekly and the number of items always changes for each ID.... Is there a simple way to do this? Could someone please point me in the right direction..

Mahalos,
Chris

 

[Tom Urtis]

If your data truly looks like that and it starts on row 1 (you did not say so I'm guessing) and you want to display the count of items for each ID in column B next to that ID name (you did not say so I am guessing that as well) then this will do what you want, seeing as you have less than 8192 ID numbers:


Sub Test1()
Application.ScreenUpdating = 0
Columns(2).Clear
Dim Area As Range, Rowz&, NLR&
NLR = Cells(Rows.Count, 1).End(xlUp).Row + 1
Rowz = 1
For Each Area In Range(Cells(1, 1), Cells(NLR, 1)).SpecialCells(2).areas
Rowz = Rowz + Area.Rows.Count + 1
Range(Area.Address).Offset(0, 1).Resize(1, 1).Value = _
Area.Rows.Count - 1 & " items"
Next Area
With Columns(2)
.HorizontalAlignment = xlRight
.AutoFit
End With
Application.ScreenUpdating = 1
End Sub

 

[chris808]

Thanks Tom,
Sorry for the missing info..but you hit it right on the head. I can see now that I wasnt going to be able to just figure that out with a function or something. Youve saved me countless hours..

I really appreciate the help.

Many Mahalos,
Chris