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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

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,425
Office Version
  1. 2019
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,276
Members
430,201
Latest member
Deepakpilla36

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
Top