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

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.

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"mino" wrote in message
...
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.

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

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

Jim Thomlinson


"mino" wrote:

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.

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

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


"mino" wrote:

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.

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

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

"Tom Ogilvy" ha scritto nel messaggio
...
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


"mino" wrote:

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.