Find/Delete skipping rows

[sbitaxi]

Hello:

Not sure how to resolve this. I'm running this bit of code to delete
rows that contain a 0 value if the Registration Fee is Not Applicable.

It works for the most part, but where it misses rows is when there are
two (or more) consecutive rows with a 0 value. I know this is because
the next "MyCell" becomes the current once the row has been deleted.
Can anyone help resolve this?

Thank you!

Steven


Set FoundCell = SrcHdrRng.Find(What:="Registration Fee")
For Each MyCell In Range(FoundCell.Address, Cells(SrcLast,
FoundCell.Column).Address)
If MyCell.Value = "NotApplicable" Then
If MyCell.Offset(0, 1).Value = 0 Then
MyCell.EntireRow.Delete
End If
End If
Next

 

[GTVT06]

Hello, Try this

Dim rng As Range
Set rng = Nothing
Set foundcell = SrcHdrRng.Find(What:="Registration Fee")
For Each mycell In Range(foundcell.Address, Cells(SrcLast,
foundcell.Column).Address)
If mycell.Value = "NotApplicable" Then
If mycell.Offset(0, 1).Value = 0 Then
If rng Is Nothing Then
Set rng = Rows(mycell.Row & ":" &
mycell.Row)
Else: Set rng = Union(rng,
Rows(mycell.Row & ":" & mycell.Row))
End If
End If
End If
Next
rng.Delete

 

[JLGWhiz]

In case it still don't work. Try this:

Dim FouncCell As Range, lstCell As Long, i As Long
Set FoundCell = SrcHdrRng.Find(What:="Registration Fee")
If Not FoundCell Is Nothing Then
lstCell = Cells(65536, FoundCell.Column).End(xlUp).Row
For i = lstCell To FoundCell.Row Step -1
If Cells(i, FoundCell.Column).Value = "NotApplicable" Then
If Cells(i, FoundCell.Column).Offset(0, 1).Value = 0 Then
Cells(i, FoundCell.Column).EntireRow.Delete
End If
End If
Next
End If

It usually works better if, when deleting rows, you start from the bottom
and work upwards. This eliminates the skipping effect due to the default
shift.

 

[Barb Reinhardt]

At the end, I'd make a slight modification.

Replace
rng.Delete
WIth

if not rng is nothing then
rng.Delete
end if

 

[sbitaxi]

Excellent!

Barb/GTVT06/JLGWhiz - Thank you! great solution.


S

 

[GTVT06]

Excellent!

Barb/GTVT06/JLGWhiz - Thank you! great solution.

S

no problem.