delete based on criteria

**numbersdntlie** · 03-25-2011, 09:34 AM

Hi all-

I have a very large excel file with 2900 rows and 25 columns. I would like an if function or macro that will compare what is in column A with column B and delete the entire row if the criteria doesnt match. For Example : items in Column A that appear more than once (ex.MAR) but has conflicting statuses is to have the rows with the status of Pending to be deleted. If there is no conflicting rows for column A item I would like it to stay. I would like the whole entire row up to column Z to be deleted for each time the macro deletes. thanks for your help.

See attached for sample data.

**pike** · 03-25-2011, 09:23 PM

Hi numbersdntlie

Try this loop

Option Explicit
Sub ntest()
    Dim xRow As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
    xRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For xRow = .Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If Trim(Cells(xRow, 1).Value) = Trim(.Cells(xRow - 1, 1).Value) And Trim(.Cells(xRow - 1, 2).Value) = "PENDING" Then
            .Cells(xRow - 1, 1).EntireRow.Delete xlShiftUp
        End If
    Next
End With
Application.ScreenUpdating = True
End Sub

**numbersdntlie** · 03-28-2011, 09:20 AM

Thank you very much, that's works. SOLVED

**royUK** · 03-28-2011, 09:53 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved. Also, please don't use VBA, macro in your Thread Titles. We know that as you've posted in the programming Forum & using them makes the search engine less efficient

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save