counting rows

[Guest]

I have a macro that will take a sorted spreadsheet, put separating blank rows
between each unique group, and add up a column which have numerical values
for each group. What I need to add to this macro is a count of the number of
rows are in each group. I want to put the count of rows for each group in
column D in the same row as the sum of the numbers.

Sub dept()


'\/data manipulation for in prog dept begins here\/


'selects all the data rows and sorts them by dept and then job number
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A1").Select


With ActiveSheet
.AutoFilterMode = False
End With

Range("A1").Select

'separates by dept and adds subtotal estimate hours for each dept
Set start = Range("D2")
i = 3
Do
Set rng = Cells(i, "D")
Set rngo = Cells(i - 1, "D")
Set rnge = Range(Cells(i, "D"), Cells(Rows.Count, "D").End(xlUp))
If rng.Value <> rngo.Value Then
rng.Resize(2).EntireRow.Insert
rngo.Offset(1, 7).Formula = "=Sum(" & _
Range(start, rngo).Offset(0, 7).Address & ")"
i = i + 2
Set start = Cells(i, "D")
End If
i = i + 1
If Cells(i - 1, "D") = "" Then Exit Do
Loop

Range("A1").Select

End Sub

 

[Guest]

rngo.Offset(1, 7).Formula = "=Sum(" & _
Range(start, rngo).Offset(0, 7).Address & ")"
' add line
rngo.offset(1,0).Formula = "=Rows(" &
Range(Start,rng0).Address & ")"

I assume you want a formula. You could just put in the count

rngo.offset(1,0).Value = Range(Start,rng0).count

 

[Guest]

Thank you very much Tom!

rngo.Offset(1, 7).Formula = "=Sum(" & _
Range(start, rngo).Offset(0, 7).Address & ")"
' add line
rngo.offset(1,0).Formula = "=Rows(" &
Range(Start,rng0).Address & ")"

I assume you want a formula. You could just put in the count

rngo.offset(1,0).Value = Range(Start,rng0).count