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..:)
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 ...
You still haven't said what the condition actually is!
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.