Delete previous row if cell contains text

[nick612hayden]

Hey all

Trying to find the best way to find a solution to:

If cell in Column A contains "Text", then delete that row, plus previous 150 rows.
or
If cell in Column A contains "Text", then delete that row, plus all blank previous rows.

In all sheets in the entire workbook

Thanks for any help!
Nick

 

[patel45]

Sub deleteaboverows() 
Dim cella As Range, rng150 As Range, rdel As Range
LR = Cells(Rows.Count, "a").End(xlUp).Row
Set Rng = Range("A1:A" & LR)
Set rdel = Rng.Cells(Rng.Rows.Count, 1)
For Each cella In Rng
    If cella.Value = "Text" Then
Set rng150 = Range(Cells(cella.Row - 150, 1), Cells(cella.Row, 1))
        rng150.Select
        Set rdel = Union(rdel, rng150)
    End If
Next
rdel.EntireRow.Delete
End Sub

 

[Michael M]

Hi Nick
There's a bit of a contradiction there....

If cell in Column A contains "Text", then delete that row, plus previous 150 rows.
or
If cell in Column A contains "Text", then delete that row, plus all blank previous rows.

What happens if the previous 150 rows are not blank ??
What happens if ALL the blank previous rows are only 5 or 10 rows ??

 

[nick612hayden]

Apologies, let's just go with any blank rows above the cell which contains "Text"

Nick

Hi Nick
There's a bit of a contradiction there....

What happens if the previous 150 rows are not blank ??
What happens if ALL the blank previous rows are only 5 or 10 rows ??

 

[nick612hayden]

bump...anyone?

Apologies, let's just go with any blank rows above the cell which contains "Text"

Nick

 

[Michael M]

Sorry Nick, been away for a few days....try,

Sub MM1()
Dim r As Long
Application.ScreenUpdating = False
For r = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Range("A" & r).Value = "Text" And Range("A" & r - 1).Value = "" Then
        Rows(r - 1).Delete
    End If
Next r
Application.ScreenUpdating = True
End Sub

 

[nick612hayden]

Thanks Michael

Tested that one...but didn't work out. I might be messing up the range I am entering...since now I just want to search column E...needs changed since last post.

Through some searching, customized this one I found from a different post for my needs, except I must now delete the one previous row to the found Text.

So I use it as follows:
Step 1: Input box to delete all blank rows
Step 2: Use it again to find Text and delete corresponding row.

The part / step where I am stumped and need to add is how to have it delete the (one) previous row to found Text. Also it's taking a quite a while to run, so wondering if I need that loop in the code at all.

Any thoughts on the above, or below code, would be appreciated to help my understanding of this. Otherwise, hope this can help others too!

Sub Macro()
    Dim c As Range
    Dim SrchRng As Range
    Dim SrchStr As String
    
Application.ScreenUpdating = False
     
    Set SrchRng = ActiveSheet.Range("E1", ActiveSheet.Range("E999999").End(xlUp))
    SrchStr = InputBox("Search data for specific field to delete that cell, plus its corresponding row. To delete blank rows, keep search empty and click OK")
    Do
        Set c = SrchRng.Find(SrchStr, LookIn:=xlValues)
        If Not c Is Nothing Then c.EntireRow.Delete
    Loop While Not c Is Nothing
    
Application.ScreenUpdating = True
     
End Sub

Sorry Nick, been away for a few days....try,

 

[Michael M]

If I understand correctly.....although the goal posts keep moving...

Sub MM1()
Dim r As Long, SrchStr As String
Application.ScreenUpdating = False
SrchStr = InputBox("Search data for specific field to delete that cell, plus its corresponding row. To delete blank rows, keep search empty and click OK")
For r = Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1
    If Range("E" & r).Value = SrchStr And Range("E" & r - 1).Value = "" Then
        Rows(r - 1).Delete
    End If
Next r
Application.ScreenUpdating = True
End Sub

 

[nick612hayden]

Sorry, hopefully I can cement the posts in completely now...sooo close to the end solution!

Still needs:

First: to delete all rows that are completely blank in the Sheet
Then: find the Text (yes, only in column E still), and delete the row that the Text in...plus delete the row above that one(which, yes, will not be blank...but still needs to be deleted)

Hopefully that won't be too horrible of an adjustment in this code for ya, you rock for doing this!

Thanks,
Nick

 

[Michael M]

Hey Nick
This will....hopefully do what you want on ALL worksheets

Sub MM1()
Dim r As Long, SrchStr As String, ws As Worksheet
Application.ScreenUpdating = False
SrchStr = InputBox("Search data for specific field to delete that cell, plus its corresponding row. To delete blank rows, keep search empty and click OK")
For Each ws In Worksheets
    ws.Activate
    Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    For r = Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1
        If Range("E" & r).Value = SrchStr Then
            Rows(r & ":" & r - 1).Delete
        End If
    Next r
Next ws
Application.ScreenUpdating = True
End Sub