Find/Delete skipping rows
 

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

Find/Delete skipping rows
 

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

Find/Delete skipping rows
 

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.

" wrote:

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

Find/Delete skipping rows
 

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

Replace
rng.Delete
WIth

if not rng is nothing then
rng.Delete
end if

--
HTH,
Barb Reinhardt



"GTVT06" wrote:

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

Find/Delete skipping rows
 

Excellent!

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


S

Find/Delete skipping rows
 

On Aug 15, 9:02*am, wrote:
Excellent!

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

S

no problem.