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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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