L
lousyiain
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
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