Code To remove rows with criteria

[divapaxo]

Trying to amend this code to get the following result

If column B = GBP then do not delete that row and if column A = code AL1234 and column B = EUR then do not delete the row everything else delete row.

Set ws = Sheets("EQUITY REPORT")
For lRow = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1 To 1 Step -1
v1 = ws.Cells(lRow, "D").Value
If Len(v1) > 0 _
And IsNumeric(v1) _
And Val(v1) = 0 Then
ws.Rows(lRow).Delete Shift:=xlUp
End If
Next lRow

Thanks.

 

[Andrew Poulsom]

Try (untested):

Set ws = Sheets("EQUITY REPORT") 
For lRow = ws.UsedRange.Rows.Count To 1 Step -1 
   v1 = ws.Cells(lRow, "D").Value 
   If Len(v1) > 0 And IsNumeric(v1) And Val(v1) = 0 Then
      If ws.Cells(lRow, "B").Value <> "GBP" Then
         If ws.Cells(lRow, "B").Value <> "EUR" And ws.Cells(lRow, "A").Value <> "AL1234" Then
            ws.Rows(lRow).Delete Shift:=xlUp 
         End If
      End If
   End If 
Next lRow

I'm not sure if you still needed your original test so I left it in.
[/code]

 

[Von Pookie]

I haven't tested this, but I think it should work:

Sub test()
Dim LastRow As Long, i As Long

With Sheets("EQUITY REPORT")
    LastRow = .Range("B65536").End(xlUp).Row

    For i = LastRow To 1 Step -1
        Select Case .Cells(i, "B").Value
            Case Is = "EUR"
                If .Cells(i, "A") <> "AL1234" Then
                    .Rows(i).Delete
                End If
            Case Is <> "GBP"
                .Rows(i).Delete
        End Select
    Next i
End With
                
End Sub

 

[Roses8]

thanks for the reply that worked