Select Case - Loop - Delete Row

[mmetzinger]

OK, so I have the following code:

Dim oneCell As Range
Dim HeaderRange As String
        Range("Z1").Select
        Range(Selection, Selection.End(xlDown)).Select
        HeaderRange = Selection.Address
        
For Each oneCell In Range(HeaderRange)
If oneCell.Value = "" Then
GoTo skip
End If
    Select Case oneCell.Value
                Case "Policy Status"
                Case "PRMPY"
                Case "Issued- Not Paid"
                Case Else
                oneCell.EntireRow.Delete
    End Select
skip:
Next oneCell

which works great except for one issue. On the case else step when it deletes the row a problem is created with the loop in that the row that is after the row that is deleted moves up but the loop goes to the next row after it. All I need is a way to tell it that when it does the delete it also needs to step back one "oneCell" but i don't have a clue how to do this.

Any help would be greatly appreciated!

 

[VoG]

You need to loop backwards

Dim LR As Long, i As Long
LR = Range("Z" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    Select Case Range("Z" & i).Value
                Case "Policy Status"
                Case "PRMPY"
                Case "Issued- Not Paid"
                Case Else
                Rows(i).Delete
    End Select
Next i

 

[Norie]

You need to loop backwards but you can't do that using For Each.

Try this.

Dim oneCell As Range

 For I = Range("Z" & Rows.Count).End(xlUp).Row To 1 Step -1        
   Set oneCell = Range("Z" & I

   Select Case oneCell.Value
          Case "Policy Status","PRMPY","Issued- Not Paid"
                Case Else
          oneCell.EntireRow.Delete
   End Select
 Next I

 

[njimack]

This can be avoided by working backwards. And you don't need to select cells to work with them...

Sub Delete()
Dim Last_Row As Long
Dim i As Long
Application.ScreenUpdating = False
Last_Row = Range("Z1").End(xlDown).Row
For i = Last_Row To 1 Step -1     
        
        Select Case Cells(i, 26)
                    Case "Policy Status"
                    Case "PRMPY"
                    Case "Issued- Not Paid"
                    Case ""
                    Case Else
                    Cells(i, 26).EntireRow.Delete
        End Select
Next i
Application.ScreenUpdating = True
End Sub

 

[mmetzinger]

Thanks for all of the replies. You all pointed out that I needed to work backwards which did resolve my issue.