Delete rows based on results of cell tests
 

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

Delete rows based on results of cell tests
 

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



" 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