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
|