Find/Delete skipping rows

S

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
 
G

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
 
J

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.
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top