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:

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

patel45

Well-known Member
Joined
Jul 15, 2012
Messages
1,953
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,751
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,751
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

nick612hayden

New Member
Joined
May 15, 2012
Messages
33

ADVERTISEMENT

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,
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,751
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

nick612hayden

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

Well-known Member
Joined
Oct 27, 2005
Messages
19,751
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,767
Messages
5,597,987
Members
414,199
Latest member
GDtoBMe

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
Top