Do loop formula

febrian91

New Member
Joined
Sep 17, 2014
Messages
10
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
 
Upvote 0
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
 
Upvote 0
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]
 
Upvote 0
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??
:)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top