Delete previous row if cell contains text

nick612hayden

New Member
Joined
May 15, 2012
Messages
33
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
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Code:
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
 
Upvote 0
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 ??
 
Upvote 0
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 ??
 
Upvote 0
Sorry Nick, been away for a few days....try,
Code:
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
 
Upvote 0
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!

Code:
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,
 
Upvote 0
If I understand correctly.....although the goal posts keep moving...:biggrin:

Code:
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
 
Upvote 0
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
 
Upvote 0
Hey Nick
This will....hopefully :biggrin: do what you want on ALL worksheets

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top