Conditional delete in a range

[Sebasti]

Hi there,

I have this below table which has data from A2 to F6 (which keeps changing every day, can even go upto A2 to F250). Basically i need to delete all the rows except with the word "Dune" in both Column B and Column E. In short i want rows 3 and 4 go from this list. I have this code which seems to be not working. Im a bit bad in VBA too ;-)

Sheets("Data").Select
Range("A1:E1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value <> "Dune" Then
Selection.EntireRow.Delete
Else: ActiveCell.Offset(1, 0).Select
End If
Loop

Range("A1").Select

 

[Sebasti]

Sorry, a small correction i want rows 4 and 5 go from this list

 

[Deepak]

There are lot's of way to do this!!

Sub Delete_Rows()
    Dim i As Long
    Application.ScreenUpdating = False
    With ActiveSheet.Range("A1:F6")
        For i = .Rows.Count To 1 Step -1
            If Not Application.CountIf(Range("A" & i & ":F" & i), "Dune") > 0 Then Rows(i).EntireRow.Delete
        Next
    End With
    Application.ScreenUpdating = True
End Sub

 

[Deepak]

Oops..

This would be more dynamic!!

Sub Delete_Rows2()
    Dim i As Long
    Application.ScreenUpdating = False
    With Sheets("Data")
        For i = .Range("A1").CurrentRegion.Rows.Count To 2 Step -1
            If Not Application.CountIf(Range("A" & i & ":F" & i), "Dune") > 0 Then Rows(i).EntireRow.Delete
        Next
    End With
    Application.ScreenUpdating = True
End Sub

 

[Sebasti]

Oops..

This would be more dynamic!!

Sub Delete_Rows2()
Dim i As Long
Application.ScreenUpdating = False
    With Sheets("Data")
        For i = .Range("A1").CurrentRegion.Rows.Count To 2 Step -1
            If Not Application.CountIf(Range("A" & i & ":F" & i), "Dune") > 0 Then Rows(i).EntireRow.Delete
        Next
    End With
Application.ScreenUpdating = True
End Sub

Thank you very much Deepak, you are my savior today

 

[Deepak]

Welcome Dear!! @Sebasti

 

[Sebasti]

Welcome Dear!! @Sebasti

One quick question Deepak! What if i want to give 2 Criterias? Like "Dune" and "Welcare"? Sorry this didnt come to my mind while asking the first question...

 

[Deepak]

One quick question Deepak! What if i want to give 2 Criterias? Like "Dune" and "Welcare"? Sorry this didnt come to my mind while asking the first question...

Use countifs instead of countif.