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

  • Thread starter Thread starter mino
  • Start date Start date
M

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.
 
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
 
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
 
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.
 
Don, Jim & Tom .. . MANY MANY THANKS !

Tom Ogilvy said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top