Hi

I have ~200 rows of data - this data is split into groups of 5-12
rows.
At the bottom of each of these groups is a total

I am trying to find the rows with the word "total" and test if the
value one cell to the right=0
if the total=0 then i want to delete all the rows between this total
and the next total which does not equal 0

I fairly amateurish with my VBA but below is what I have so far
(probably completely wrong)
A sample of what the data might look is at the bottom

Thanks for your help
Iain


Sub ZeroChunkDelete

Dim lastrow As Long
Dim n As Long
Dim m As Long
Dim BottomZeroTotal As Long
Dim TopZeroTotal As Long

lastrow = Sheets("Print Out").Cells(Cells.Rows.Count, "B").End
(xlDown).Row
For n = lastrow To 1 Step -1
If UCase(Cells(n, 1).Value) = "Total" And Selection.End
(xlToRight).Value = 0 Then BottomZeroTotal = n
Next n

For m = n To 1 Step -1
If UCase(Cells(m, 1).Value) = "Total" And Selection.End
(xlToRight).Value = 0 Then TopZeroTotal = m
Next m
Worksheets("Print Out").Rows(TopZeroTotal & ":" &
BottomZeroTotal).Delete
End Sub


Data sample
A B
2
0
1
total 3
}
0 }
0 }I want to delete these rows
0 }
total 0 }

1
0
total 1

Make sure there are no blank characters in the cell with the row total. I
improved your code. My code is fully tested. One problem I say with your
code that was wrong is shown below

from
If UCase(Cells(m, 1).Value) = "Total" 'this would never matc h
to
If UCase(Cells(m, 1).Value) = "TOTAL"



Sub ZeroChunkDelete()

Dim LastRow As Long
Dim RowCount As Long
Dim EndRow As Long
Dim Remove As Boolean

LastRow = Sheets("Print Out").Range("B" & Rows.Count).End(xlUp).Row
Remove = False
For RowCount = LastRow To 1 Step -1
If UCase(Range("A" & RowCount)) = "TOTAL" Then
If Remove = True Then
Rows((RowCount + 1) & ":" & EndRow).Delete
End If
If Range("B" & RowCount).Value = 0 Then
EndRow = RowCount
Remove = True
Else
Remove = False
End If
End If
Next RowCount
If Remove = True Then
Rows("1:" & EndRow).Delete
End If

End Sub



"(E-Mail Removed)" wrote:

> Hi
>
> I have ~200 rows of data - this data is split into groups of 5-12
> rows.
> At the bottom of each of these groups is a total
>
> I am trying to find the rows with the word "total" and test if the
> value one cell to the right=0
> if the total=0 then i want to delete all the rows between this total
> and the next total which does not equal 0
>
> I fairly amateurish with my VBA but below is what I have so far
> (probably completely wrong)
> A sample of what the data might look is at the bottom
>
> Thanks for your help
> Iain
>
>
> Sub ZeroChunkDelete
>
> Dim lastrow As Long
> Dim n As Long
> Dim m As Long
> Dim BottomZeroTotal As Long
> Dim TopZeroTotal As Long
>
> lastrow = Sheets("Print Out").Cells(Cells.Rows.Count, "B").End
> (xlDown).Row
> For n = lastrow To 1 Step -1
> If UCase(Cells(n, 1).Value) = "Total" And Selection.End
> (xlToRight).Value = 0 Then BottomZeroTotal = n
> Next n
>
> For m = n To 1 Step -1
> If UCase(Cells(m, 1).Value) = "Total" And Selection.End
> (xlToRight).Value = 0 Then TopZeroTotal = m
> Next m
> Worksheets("Print Out").Rows(TopZeroTotal & ":" &
> BottomZeroTotal).Delete
> End Sub
>
>
> Data sample
> A B
> 2
> 0
> 1
> total 3
> }
> 0 }
> 0 }I want to delete these rows
> 0 }
> total 0 }
>
> 1
> 0
> total 1
>
>
>
>