search word, then delete row above, IF...

abound1

Board Regular
Joined
Aug 8, 2005
Messages
84
I need some vba code that will search down a column A for a keyword, and when found, if the 2 cells above that word both have data, then delete the row directly above it. If there is only 1 cell directly above with data (and the one above that one is empty), then do nothing.

Example:
A 1
A 2
A 3 data
A 4 data (<- since cells A3 & A4 contain data, therefore DELETE THIS ROW)
A 5 KEYWORD
A 6
A 7
A 8 (< since this cell is empty, therefore DO NOTHING)
A 9 data
A 10 KEYWORD
A 11

In other words:
the 1st instance of search keyword found is at A5
Since the two cells above the keyword (A3 & A4) both have data, therefore delete the row above the keyword.

The 2nd instance of search keyword is at A10
Since only the one cell (A9) above the keyword contains data, therefore do nothing, and go to the next search, etc…

I appreciate your help.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

How about
Code:
Sub eee()
  For i = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
    If Cells(i, 1) = "KEYWORD" And Not (IsEmpty(Cells(i - 1, 1))) And Not (IsEmpty(Cells(i - 2, 1))) Then
      Cells(i - 1, 1).EntireRow.Delete
    End If
  Next i
    
    
End Sub


Tony
 

abound1

Board Regular
Joined
Aug 8, 2005
Messages
84
thanks a lot Tony.

I forgot to mention that the keyword is not alone in the cell - there is other data in the cell along with the KEYWORD - so the search value needs to allow for this variation.

But I did test the code with the entire cell contents pasted in the code and it seems to work.

So can you adjust the code to accomodate for partial cell contents search.

Thanks again.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Try

Code:
Sub eee()
  For i = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
    If InStr(1, Cells(i, 1), "KEYWORD") > 0 And Not (IsEmpty(Cells(i - 1, 1))) And Not (IsEmpty(Cells(i - 2, 1))) Then
      Cells(i - 1, 1).EntireRow.Delete
    End If
  Next i
    
    
End Sub


Tony
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

Hi

Try

Code:
Sub eee()
  For i = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
    If InStr(1, Cells(i, 1), "KEYWORD") > 0 And Not (IsEmpty(Cells(i - 1, 1))) And Not (IsEmpty(Cells(i - 2, 1))) Then
      Cells(i - 1, 1).EntireRow.Delete
    End If
  Next i
    
    
End Sub


Tony
 

abound1

Board Regular
Joined
Aug 8, 2005
Messages
84
Works great!
Just what I needed.
Thanks a lot Tony.


BTW, I could use some help with another similar bit of code.
This one finds cells matching the “keyword” and then moves them up 1 row and over 2 columns.
I'd like to delete the old rows the cells used to reside in. Can you help with this one too.
Thanks.
Code:
Sub MoveCells()
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    If Len(WorksheetFunction.Substitute(c, "KEYWORD", "")) <> Len(c) Then
        c.Cut c.Offset(-1, 2)
    End If
Next c
End Sub
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Try adding

c.entirerow.delete after the c.cut line


Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,113,919
Messages
5,545,028
Members
410,647
Latest member
bernardazar
Top