Delete row if cell contains a certain value AND the row above is empty

[hip2b2]

I need to delete a row where the value in Col B (of that same row( contains specific text, AND the row above is blank.

Thanks

hip

 

[maabadi]

Try this:

Sub DeleteRowIf()
Dim i As Long, lr As Long
lr = Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To lr
If Range("B" & i).Value = "Specific" And Range("B" & i - 1).Value = "" Then
Cells(i, 2).EntireRow.Delete
End If
Next i
End Sub

 

[hip2b2]

Thanks it works perfectly!!! except I want to run it from within a "dogs breakfast" of a maco and the code fails with " , lr As Long".

Any suggestion?

 

[maabadi]

I think you have two lr declare at your macro. Change lr to Lr1 at all of my code and test it. if again error, please upload total macro here to check.

 

[hip2b2]

And that did it.

Thanks 10^6

hip

 

[maabadi]

You're Welcome & glad we can help.

 

[hip2b2]

Is it possible to modify the code to make the contents less specific, so that the Cell in Col B contains "Specific" and any other text AND the row above is blank...

I tried the following (which of course did not work).

Dim i As Long, lr1 As Long
    lr1 = Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 To lr
    If Range("B" & i).Value = "Specific" & strName & "*" And Range("B" & i - 1).Value = "" Then
    Cells(i, 2).EntireRow.Delete
    End If
    Next i
    End With
    h.AutoFilterMode = False

Thanks

hip

 

[maabadi]

Use Like Option.
Try This:

Dim i As Long, lr1 As Long
    lr1 = Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 To lr
    If Range("B" & i).Value  Like "*" & "Specific" &  "*" And Range("B" & i - 1).Value = "" Then
    Cells(i, 2).EntireRow.Delete
    End If
    Next i
    End With
    h.AutoFilterMode = False

 

[hip2b2]

Thank you 10^6

hip

 

[maabadi]

You're Welcome.