Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a line | Excel Worksheet Functions | |||
inserting a blank row | Excel Discussion (Misc queries) | |||
Help with inserting a blank line on condition | Excel Programming | |||
How do you add a blank line automatically after the Subtotal line | Excel Worksheet Functions | |||
Inserting a line.... | Excel Programming |