# Do loop formula

#### febrian91

##### New Member
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?

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You have to loop backwards when deleting rows

Code:
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

Am No VBA expert but shouldn't that simply be

Code:
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

Try something like
Code:
[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]

You have to loop backwards when deleting rows

Code:
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??

Perhaps

Code:
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

Replies
3
Views
260
Replies
8
Views
308
Replies
8
Views
330
Replies
1
Views
528
Replies
7
Views
381

1,218,808
Messages
6,144,605
Members
450,559
Latest member
kwenda farai

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back