Group and Delete
 

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

Group and Delete
 

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 wrote:
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

Group and Delete
 

Thanks James

This worked really well. Appreciate your help.

Take care

Chad

Zone wrote:

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 wrote:
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