do until

[enfantter]

Im erasing some lines in a sheet based on a condition wich is put in a textbox.

Several line should be erased since they have the same name. I doing something like this,

for i...
if ..
end if
next i

and im trying to make it run until it can find that the condition is true anywhere. This is where i stumble.

do
for i...
if ..
end if
next i
loop until ????

does anybody know the solution??!

[enfantter]

Please tell me if this post i lacking info..:)

[rory]

You already have the solution in general terms, and we can't be any more specific than what you have without knowing more detail! :)

[enfantter]

Ok, here is the actual code:


Private Sub fjern_click()
Do
For i = 0 To 500
If Cells(i + 9, 1).Value = Sletopg.Text Then
Rows(i + 9).Select
Selection.delete Shift:=xlUp
End If
Next i
Loop until
End Sub

specifically the problem is what to put after the until ...

[rory]

You still haven't said what the condition actually is!

[rory]

PS If you are going to delete rows, you should loop backwards - i.e. use:

For i = 500 to 0 Step -1

[enfantter]

thnx
the condition to stop the loop is when there is not such Cells(i + 9, 1).Value that equals Sletopg.Text anymore

[RECrerar]

Are all the cells where Cells(i + 9, 1).Value = Sletopg.Text simultaneous or are there likely to be cells where the condition is not met inbetween cells that contain sletopg.text?

You could possible do a count of the number of times sletopg.text appears in the range and then count down from this value everytime a row is deleted and then exit the for loop when count = 0.




Private Sub fjern_click()
count = WorksheetFunction.CountIf(Range(Cells(9, 1), Cells(509, 1)), sletopg.text)
For i = 500 To 0 Step -1
If Cells(i + 9, 1).Value = Sletopg.Text Then
Rows(i + 9).Select
Selection.delete Shift:=xlUp
count = count - 1
End If
If count = 0 then Exit For
Next i
End Sub


I don't think a do until loop round the outside wouldn't stop the for next loop exectuing to completion

[mdmackillop]

Without a loop


Option Explicit

Sub DelRows()
Dim r As Range, FC As Range
Set FC = Cells(8, 1)
Set r = Range(FC, Cells(509, 1))
r.AutoFilter 1, Sletopg.Text
Set r = Range(Cells(9, 1), Cells(509, 1)).SpecialCells(xlCellTypeVisible)
r.EntireRow.Delete
FC.AutoFilter
End Sub