PDA

View Full Version : do until



enfantter
11-30-2007, 05:37 AM
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
11-30-2007, 05:59 AM
Please tell me if this post i lacking info..:)

rory
11-30-2007, 06:07 AM
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
11-30-2007, 06:08 AM
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
11-30-2007, 06:15 AM
You still haven't said what the condition actually is!

rory
11-30-2007, 06:16 AM
PS If you are going to delete rows, you should loop backwards - i.e. use:
For i = 500 to 0 Step -1

enfantter
11-30-2007, 06:20 AM
thnx
the condition to stop the loop is when there is not such Cells(i + 9, 1).Value that equals Sletopg.Text anymore

RECrerar
11-30-2007, 06:38 AM
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
11-30-2007, 07:15 AM
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