Group and Delete

[Chad]

Hi

I have a list of cost centres sorted in order. I am trying,
unsuccessfully to locate some code which will delete the rows where the
sum of the Cost centres equals 0. This is in a bid to reduce the size
of the file, once reduced I will pivot table the results. Here is an
example of the file;


CCs Value

1211 100
1211 (100)
1222 20
1222 30
1222 10


After the code has run I would like the file to look like this

CC Value

1222 20
1222 30
1222 10

Thanks in advance.

Chad

 

[Zone]

Chad, this seems to work. Not sure if I've really tested it that
thoroughly. Make a backup copy of your file before using. Then copy
this code and put in a standard module. Note that it only works on the
active sheet. I assumed your cost centers are in column A and your
values in column B. Also, it appears that row 2 is blank on your
sheet, so I did it that way. This may not be the most elegant
solution! Let me know how it works for you. James

Sub GroupAndDelete()
Dim sRow As Long, eRow As Long, k As Long, ShtBtm As Long
ShtBtm = Cells(65536, "a").End(xlUp).Row
eRow = ShtBtm
For k = ShtBtm - 1 To 2 Step -1
If Cells(k, "a") = Cells(eRow, "a") Then
sRow = k
Else
If eRow <> sRow Then
If Application.Sum(Range(Cells(sRow, "b"), Cells(eRow,
"b"))) = 0 Then
Rows(CStr(sRow) & ":" & CStr(eRow)).EntireRow.Delete
End If
End If
eRow = k
sRow = k
End If
Next k
End Sub

 

[Chad]

Thanks James

This worked really well. Appreciate your help.

Take care

Chad