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?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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
 

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
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]
 

febrian91

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,159
Messages
5,527,149
Members
409,749
Latest member
esmarques

This Week's Hot Topics

Top