Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up a "roll over" counter in excel 2003? | Excel Worksheet Functions | |||
multiple file uploading - runtime error'13': type mismatch "While Counter <= UBound(FName)" | Excel Discussion (Misc queries) | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming |