PDA

View Full Version : Combining IF criteria in DO LOOP



AZIQN
02-26-2009, 09:17 AM
Hi, I'm wondering how to combine IF statement criteria in a DO LOOP so I can eliminate unnecessary lines of repetitive loops...here is what I have so far:

Range("K1").Select
Do Until Selection.Value = ""
If Selection.Value = "INVEST ACCOUNT CREDIT" Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Loop

Range("K1").Select
Do Until Selection.Value = ""
If Selection.Value = "INTEREST CREDIT" Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Loop

Range("K1").Select
Do Until Selection.Value = ""
If Selection.Value = "INVEST ACCOUNT DEBIT" Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Loop

Range("K1").Select
Do Until Selection.Value = ""
If Selection.Value = "CONTROLLED DISBURSEMENT DEBIT" Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Loop

Thanks for your help!

Bob Phillips
02-26-2009, 09:24 AM
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
For i = 1 To Lastrow

If .Cells(i, "K").Value = "INVEST ACCOUNT CREDIT" Or _
.Cells(i, "K").Value = "INTEREST CREDIT" Or _
.Cells(i, "K").Value = "INVEST ACCOUNT DEBIT" Then

.Rows(i).Delete
End If
Next i
End With

Sagy
02-26-2009, 09:59 AM
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
For i = 1 To laqstrow

If .Cells(i, "K").Value = "INVEST ACCOUNT CREDIT" Or _
.Cells(i, "K").Value = "INTEREST CREDIT" Or _
.Cells(i, "K").Value = "INVEST ACCOUNT DEBIT" Then

.Rows(i).Delete
End If
Next i
End With

Shouldn't the loop be
For i = LastRow to 1 Step -1
Otherwise when row x is deleted row x+1 will become row x and will not be checked since i will be incremented to x+1.

AZIQN
02-26-2009, 10:07 AM
Thank you xld and Sagy!

Sagy, your change was correct. The initial way did neglect a few lines, but " i=LastRow to 1 Step -1 " fixed it. Thanks again!