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?
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

0
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.
0
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

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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.
0
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?
0
W.E.BAuthor Commented:
Correct
0
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

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

it's Deleting All Rows
0
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.
0
W.E.BAuthor Commented:
Please see attached.

Thank you,
Sample.xlsx
0
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

0
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

0
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...
0

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.
0
Saurabh Singh TeotiaCommented:
Wass_QA,

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

Saurabh...
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.