Select and delete row

[PipBoy808]

Hi,

I'm trying to write a condition where if the value in column O isn't "XYZ" then that row needs to be deleted:

For i = 1 To Array.Rows.Count
  If Cells(5 + i, 15).Value <> "XYZ" Then
  'Delete row i
  End If

I keep getting the syntax of the 'delete row i' part wrong. Can anyone help me out?

 

[Smallman]

Hi PipBoy

If Col O is 1000 rows long you need to perform 2000 actions. 1.Test for the Condition and 2. perform the Action. Now multiply this by 1000 iterations to get your 2000 actions. Just cut to the heart of the problem and perform 1 action for all items in your list.

This does that.

Sub Goski()
    Range("O5", Range("O" & Rows.Count).End(xlUp)).AutoFilter 1, "<>" & "XYZ"
    Range("O6", Range("O" & Rows.Count).End(xlUp)).EntireRow.Delete
    [o5].AutoFilter
End Sub

Take care

Smallman

 

[Hui]

For i = 1 To Array.Rows.Count
  If Cells(5 + i, 15).Value <> "XYZ" Then Row(5+i).Delete  
End If