Excel Delete Rows Macro

Hello,
Can you please help with an Excel Macro that Deletes any Row that doesn't Contain  
Column "J"                  Chris
Column "O"                Chris
Column "J"                  Tony
Column "O"                Tony

Any help is appreciated,
Thanks
W.E.BAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
If you have headers then change the FIRST_DATA_ROW constant.
Sub DeleteRows()
Dim lngRow As Long
Const FIRST_DATA_ROW = 1

With ActiveSheet
    For lngRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row To FIRST_DATA_ROW Step -1
        If .Cells(lngRow, "J") = "Chris" Or .Cells(lngRow, "O") = "Tony" Then
            ' Don't delete
        Else
            .Cells(lngRow, "A").EntireRow.Delete
        End If
    Next
End With
End Sub

Open in new window

Martin LissOlder than dirtCommented:
The above assumes that if "Chris" or "James" is in either columns J or O then that row should not be deleted.
Martin LissOlder than dirtCommented:
If either Chris or Tony need to be in both columns J and O then...

Sub DeleteRows()
Dim lngRow As Long
Const FIRST_DATA_ROW = 1

With ActiveSheet
    For lngRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row To FIRST_DATA_ROW Step -1
        If .Cells(lngRow, "J") = "Chris" And .Cells(lngRow, "O") = "Chris" Or _
           .Cells(lngRow, "J") = "Tony" And .Cells(lngRow, "O") = "Tony" Then
            ' Don't delete
        Else
            .Cells(lngRow, "A").EntireRow.Delete
        End If
    Next
End With
End Sub

Open in new window

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

W.E.BAuthor Commented:
Hi Martin,
thank you for your help,
I tested the macro,
it is still keeping some rows.

I only want to keep Rows where the names (Chris and Tony) exists.
Chris To Chris
Chris To Tony
Tony To Tony
Tony To Chris.

For Example, , Right now, it is keeping rows from
John To Chris
Chris to Samantha
Joe to Tony
Tony To Kevin

And yes, I have headers.

thanks again.
Martin LissOlder than dirtCommented:
OK I assume then that you only want to keep rows where...

Both J and O = Chris
J is Chris and O is Tony
J is Tony and O is Chris
Both J and O = Tony

Is that correct?
W.E.BAuthor Commented:
Correct
Martin LissOlder than dirtCommented:
This keeps those 4 conditions and deletes the rest.

Sub DeleteRows()
Dim lngRow As Long

With ActiveSheet
    For lngRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row To 2 Step -1
        If .Cells(lngRow, "J") = "Chris" And .Cells(lngRow, "O") = "Chris" Or _
           .Cells(lngRow, "J") = "Tony" And .Cells(lngRow, "O") = "Tony" Or _
           .Cells(lngRow, "J") = "Tony" And .Cells(lngRow, "O") = "Chris" Or _
           .Cells(lngRow, "J") = "Chris" And .Cells(lngRow, "O") = "Tony" Then
            ' Don't delete
        Else
            .Cells(lngRow, "A").EntireRow.Delete
        End If
    Next
End With
End Sub

Open in new window

W.E.BAuthor Commented:
Thank you for your help and time,

it's Deleting All Rows
Martin LissOlder than dirtCommented:
Please put together a sample workbook with some names and a column like perhaps Z that says "delete" or "keep" because I guess I'm not understanding what you need.
W.E.BAuthor Commented:
Please see attached.

Thank you,
Sample.xlsx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this....
Sub DeleteRows()
Dim lr As Long
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = False
Columns(16).Insert
Range("P2:P" & lr).Formula = "=IF(OR(AND(ISNUMBER(SEARCH(""Chris"",J2)),ISNUMBER(SEARCH(""Chris"",O2))),AND(ISNUMBER(SEARCH(""Tony"",J2)),ISNUMBER(SEARCH(""Tony"",O2)))),"""",1)"
On Error Resume Next
Range("P2:P" & lr).SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
Columns(16).Delete
Application.ScreenUpdating = True
MsgBox "Done!", vbInformation
End Sub

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Both J and O = Chris
J is Chris and O is Tony
J is Tony and O is Chris
Both J and O = Tony
Based on the above assumption.

Please replace the previous code with this one.....
Sub DeleteRows()
Dim lr As Long
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = False
Columns(16).Insert
Range("P2:P" & lr).Formula = "=IF(AND(OR(ISNUMBER(SEARCH(""Chris"",J2)),ISNUMBER(SEARCH(""Tony"",J2))),OR(ISNUMBER(SEARCH(""Chris"",O2)),ISNUMBER(SEARCH(""Tony"",O2)))),"""",1)"
On Error Resume Next
Range("P2:P" & lr).SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
Columns(16).Delete
Application.ScreenUpdating = True
MsgBox "Done!", vbInformation
End Sub

Open in new window

Saurabh Singh TeotiaCommented:
You can alternatively use the following code too..

Sub deleterows()
    Dim rng As Range, cell As Range
    Dim lr As Long, r As Range
    lr = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Set rng = Range("J2:J" & lr)

    For Each cell In rng

        If (InStr(1, cell.Value, "tony", vbTextCompare) > 0 And InStr(1, cell.Offset(0, 5).Value, "chris", vbTextCompare) > 0) Or (InStr(1, cell.Value, "chris", vbTextCompare) > 0 And InStr(1, cell.Offset(0, 5).Value, "chris", vbTextCompare) > 0) Or (InStr(1, cell.Value, "chris", vbTextCompare) > 0 And InStr(1, cell.Offset(0, 5).Value, "tony", vbTextCompare) > 0) Or (InStr(1, cell.Value, "tony", vbTextCompare) > 0 And InStr(1, cell.Offset(0, 5).Value, "tony", vbTextCompare) > 0) Then
        Else

            If r Is Nothing Then
                Set r = cell
            Else
                Set r = Union(r, cell)
            End If

        End If

    Next cell

    If Not r Is Nothing Then r.EntireRow.Delete


End Sub

Open in new window


Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
W.E.BAuthor Commented:
Thank you guys.
Saurabh Singh Teotia macro worked perfect and fast.
Saurabh Singh TeotiaCommented:
Wass_QA,

You are welcome..Always happy to help.. :-)

Saurabh...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.