Do loop formula

[febrian91]

Dear Excel Masters,

I wanna ask "do loop" formula. The formula which i have used, But it still debug. Here is my formula :

Sub TEst()

Dim c As Range
Set c = Range("D4:D24")

Do Until c.Value = "21"
If c.Value <> 21 Then
c.EntireRow.Delete
End If

Loop


End Sub

so i want excel to delete entire row between D4:D24 if not meet the criteria, but if it meets with criteria excel will stop to search data. What's wrong with my formula?

 

[VoG]

You have to loop backwards when deleting rows

Sub TEst()
Dim i As Long
For i = 24 To 4 Step -1
    If Range("D" & i).Value <> 21 Then Rows(i).Delete
Next i
End Sub

 

[Special-K99]

Am No VBA expert but shouldn't that simply be

Sub TEst()

Dim c As Range
Set c = Range("D4:D24")

Do Until c.Value = 21
c.EntireRow.Delete
Loop

UPDATE: Ignore this and go with VoG's answer

 

[WarPigl3t]

Try something like

[COLOR=#333333]Sub TEst()[/COLOR]
[COLOR=#333333]     Dim c As Excel.Range[/COLOR]
[COLOR=#333333]    c = "D4:D24"[/COLOR]
[COLOR=#333333]     For Each cell in c[/COLOR]
[COLOR=#333333]          If cell <> 21 Then[/COLOR]
[COLOR=#333333]               Rows(cell).Delete[/COLOR]
[COLOR=#333333]          End If[/COLOR]
[COLOR=#333333]     Next c[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[febrian91]

You have to loop backwards when deleting rows

Sub TEst()
Dim i As Long
For i = 24 To 4 Step -1
    If Range("D" & i).Value <> 21 Then Rows(i).Delete
Next i
End Sub

thx sir for your help. ill try your suggestion altough find some trouble.
The result is Value 21 is deleted also and beside of that, i want macro stop deleted value if <> 21 in character bellow so i think the better way is using do until formula.
Do you have advice for my formula??

 

[VoG]

Perhaps

Sub TEst()
Dim c As Range, r As Range, cell As Range
Set c = Range("D4:D24")
For Each cell In c
    If cell.Value <> 21 Then
        If r Is Nothing Then
            Set r = cell
        Else
            Set r = Union(cell, r)
        End If
    Else
        Exit For
    End If
Next cell
If Not r Is Nothing Then r.EntireRow.Delete
End Sub