for each c in range.. how set counter "minus 1"?

[mino]

Hi.
In my code, I delete the entire row of a particolar cell 'c' (if tel = c).
However, the loop "next c" jump to next one, but the real new 'c' is in the
same row, because actual 'c' in same row is deleted.
How can set c counter minus 1?

[...]
For Each c In Range("F:F")
If tel = c Then
Rows((c.Row)).Delete
End If
End If
Next c
[...]

Thanks to all
M.

 

[Don Guillett]

You don't say what tel is but
the trick is to work from the bottom up and you really don't want to check
the WHOLE column.

for i=cells(rows.count,"F").end(xlup).row to 2 step-1
if cells(i,"f")=tel then rows(i).delete
next i

 

[Jim Thomlinson]

When you are traversing through a range with a range object in a for next
loop you will run into a lot of problems if you start deleting rows as that
changes the range you were traversing. Even if you could change your c to
look at teh right column you will still have problems because Range("F:F") is
changing. There are 2 ways to deal with this. One is to do what Don has
posted using a counter and moving from the bottom up. The other is to not do
any deleting until after you have exited the loop. To do that you want to
union a single large range together as you go...

dim rngAll as range
For Each c In Range("F:F")
If tel = c Then
if rngAll is nothing then
set rngall = c
else
set rngall = union(rngall, c)
end if
End If
End If
Next c
if not rngall is nothing then rngall.entirerow.delete

 

[Tom Ogilvy]

If you don't have any formulas that produce error values in the column:

Sub DeleteRows()
Dim r As Range, tel As String
tel = "ABC"
Columns(6).Replace _
What:=tel, _
Replacement:="=na()", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
On Error Resume Next
Set r = Columns(6).SpecialCells(xlFormulas, _
xlErrors)
On Error GoTo 0
If Not r Is Nothing Then
r.EntireRow.Delete
End If
End Sub

and less than 8190 separate areas to delete.

 

[mino]

Don, Jim & Tom .. . MANY MANY THANKS !

If you don't have any formulas that produce error values in the column:

Sub DeleteRows()
Dim r As Range, tel As String
tel = "ABC"
Columns(6).Replace _
What:=tel, _
Replacement:="=na()", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
On Error Resume Next
Set r = Columns(6).SpecialCells(xlFormulas, _
xlErrors)
On Error GoTo 0
If Not r Is Nothing Then
r.EntireRow.Delete
End If
End Sub

and less than 8190 separate areas to delete.

--
Regards,
Tom Ogilvy

Hi.
In my code, I delete the entire row of a particolar cell 'c' (if tel =
c).
However, the loop "next c" jump to next one, but the real new 'c' is in
the
same row, because actual 'c' in same row is deleted.
How can set c counter minus 1?

[...]
For Each c In Range("F:F")
If tel = c Then
Rows((c.Row)).Delete
End If
End If
Next c
[...]

Thanks to all
M.