Inserting a blank line
 

I have a column sorted by cities and a column next to that with the numbers
that can be 5, 10, 15, 25 or 30. I need to insert a space between cities and
have a count how many 5s, 10s, 15s etc there are.

column A: column D:
Boston 30
Boston 15
Boston 5
Boston 15

(in column D) 5 1(in column E)
15 2
30 1

Detroit 10

10 1

Orlando 10
Orlando 10
Orlando 10

10 3

Inserting a blank line
 

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow + 1 To 2 Step -1

If .Cells(i, "A").Value < .Cells(i - 1, "A").Value Then

For j = 30 To 5 Step -5

.Rows(i).Insert
.Cells(i, "D").Value = j
.Cells(i, "E").Formula = _
"=SUMPRODUCT(--(A1:A" & LastRow & "=""" & _
.Cells(i - 1, "A").Value & """),--(B1:B" &
LastRow & "=" & j & "))"
If .Cells(i, "E").Value = 0 Then Rows(i).Delete
Next j
End If
Next i

End With

End Sub

--
__________________________________
HTH

Bob

"Bob" wrote in message
...
I have a column sorted by cities and a column next to that with the numbers
that can be 5, 10, 15, 25 or 30. I need to insert a space between cities
and
have a count how many 5s, 10s, 15s etc there are.

column A: column D:
Boston 30
Boston 15
Boston 5
Boston 15

(in column D) 5 1(in column E)
15 2
30 1

Detroit 10

10 1

Orlando 10
Orlando 10
Orlando 10

10 3