Macro to Search and Delete Rows

[EJB74]

Hello,

I need the Column R to be search to see if it contains the value in Column C and for each row that does not contain the value in Column C to have the row deleted.

The below is a sample of the data:


CR Number FRS Type FRS ID Business Risk GxP Risk Other Regulatory Risk FRS Name Doc Version Approval Date Target Cycle Target Release Test Set Test Category Test ID Run ID Execution Status Step # Requirement Number Notes
CR 8000047418 Document 26114 High Business Risk N/A GxP Yes Other Regulatory WRICEFP #02577 Correction VAT Invoice Form 3 2017-09-12 15:13:14 CPID 8000047657 - Release 8000047659 2017 Change Releases FUT CR 47418 Unit 14179 59710 Passed Step 1 26114
CR 8000047417 Document 26116 High Business Risk N/A GxP Yes Other Regulatory WRICEFP #02578 VAT Invoice Form 3 2017-09-12 15:14:32 CPID 8000047657 - Release 8000047659 2017 Change Releases FUT CR 47417 Unit 13749 59722 Passed Step 1 26116
CR 8000047418 Functional 26115 High Business Risk N/A GxP Yes Other Regulatory Correction VAT Invoice Creation 3 2017-09-12 15:13:13 CPID 8000047657 - Release 8000047659 2017 Change Releases ST CR 47418 Verification 14222 59781 Passed Step 14 26115
CR 8000047418 Functional 26115 High Business Risk N/A GxP Yes Other Regulatory Correction VAT Invoice Creation 3 2017-09-12 15:13:13 CPID 8000047657 - Release 8000047659 2017 Change Releases ST CR 47418 Verification 14222 59781 Passed Step 22 26115
CR 8000047417 Functional 26115 High Business Risk N/A GxP Yes Other Regulatory Correction VAT Invoice Creation 3 2017-09-12 15:13:13 CPID 8000047657 - Release 8000047659 2017 Change Releases ST CR 47417 Verification 14222 59801 Passed Step 3 26118;26117
CR 8000047417 Functional 26115 High Business Risk N/A GxP Yes Other Regulatory Correction VAT Invoice Creation 3 2017-09-12 15:13:13 CPID 8000047657 - Release 8000047659 2017 Change Releases ST CR 47417 Verification 14222 59801 Passed Step 4 26117
CR 8000047417 Functional 26115 High Business Risk N/A GxP Yes Other Regulatory Correction VAT Invoice Creation 3 2017-09-12 15:13:13 CPID 8000047657 - Release 8000047659 2017 Change Releases ST CR 47417 Verification 14222 59801 Passed Step 14 26115
CR 8000047417 Functional 26115 High Business Risk N/A GxP Yes Other Regulatory Correction VAT Invoice Creation 3 2017-09-12 15:13:13 CPID 8000047657 - Release 8000047659 2017 Change Releases ST CR 47417 Verification 14222 59801 Passed Step 22 26115
CR 8000047418 Functional 26117 High Business Risk N/A GxP Yes Other Regulatory VAT Invoice Creation 3 2017-09-12 15:14:30 CPID 8000047657 - Release 8000047659 2017 Change Releases ST CR 47418 Verification 14222 59781 Passed Step 14 26115
CR 8000047418 Functional 26117 High Business Risk N/A GxP Yes Other Regulatory VAT Invoice Creation 3 2017-09-12 15:14:30 CPID 8000047657 - Release 8000047659 2017 Change Releases ST CR 47418 Verification 14222 59781 Passed Step 22 26115
CR 8000047417 Functional 26117 High Business Risk N/A GxP Yes Other Regulatory VAT Invoice Creation 3 2017-09-12 15:14:30 CPID 8000047657 - Release 8000047659 2017 Change Releases ST CR 47417 Verification 14222 59801 Passed Step 3 26118;26117
CR 8000047417 Functional 26117 High Business Risk N/A GxP Yes Other Regulatory VAT Invoice Creation 3 2017-09-12 15:14:30 CPID 8000047657 - Release 8000047659 2017 Change Releases ST CR 47417 Verification 14222 59801 Passed Step 4 26117
CR 8000047417 Functional 26117 High Business Risk N/A GxP Yes Other Regulatory VAT Invoice Creation 3 2017-09-12 15:14:30 CPID 8000047657 - Release 8000047659 2017 Change Releases ST CR 47417 Verification 14222 59801 Passed Step 14 26115
CR 8000047417 Functional 26117 High Business Risk N/A GxP Yes Other Regulatory VAT Invoice Creation 3 2017-09-12 15:14:30 CPID 8000047657 - Release 8000047659 2017 Change Releases ST CR 47417 Verification 14222 59801 Passed Step 22 26115

 

[mumps]

Are you saying for example, if R2 is not the same as C2 then you want row 2 to be deleted. If R25 is not the same as C25 then you want row 25 to be deleted. Is this correct?

 

[EJB74]

So in my example R6 has vaules 26118;26117 and C6 has value 26115, since the value 26115 is not contained in R6 the row should be deleted. But R12 has values 26118;26117 and C12 has value 26117; since the value 26117 is in R12 the row should not be deleted. Basically C will only contain 1 value, but R might contain multiple values. Any value in C needs to be in the values from R or the row should be deleted.

 

[mumps]

Try:

Sub DelRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    For x = LastRow To 2 Step -1
        If InStr(1, Cells(x, "R"), Cells(x, "C")) = 0 Then
            Rows(x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

If col R is the last column of data, try

Sub Match_delRw()

    With Range("S2:S" & Range("R" & Rows.Count).End(xlUp).Row)
        .Formula = "=FIND(C2,R2)"
        On Error Resume Next
        .SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
        On Error GoTo 0
    End With
    Columns(19).Delete

End Sub

 

[Rick Rothstein]

If col R is the last column of data, try

Sub Match_delRw()

    With Range("S2:S" & Range("R" & Rows.Count).End(xlUp).Row)
        .Formula = "=FIND(C2,R2)"
        On Error Resume Next
        .SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
        On Error GoTo 0
    End With
    Columns(19).Delete

End Sub

This is similar to your code, but instead of putting formula results in Column S, it puts an "X" in the cells your formulas return an error to. That way, by changing the SpecialCells to look for constants in Column S and deleting those rows, it eliminates the need to have to delete anything in Column S at the end of the code because the only thing my code puts in Column S gets deleted when the row itself is deleted...

[table="width: 500"]
[tr]
	[td]Sub Match_delRw()

    With Range("S2:S" & Range("R" & Rows.Count).End(xlUp).Row)
        .Value = Evaluate("IF(ISNUMBER(FIND(" & .Offset(, -16).Address & "," & .Offset(, -1).Address & ")),"""",""X"")")
        On Error Resume Next
        .SpecialCells(xlConstants).EntireRow.Delete
        On Error GoTo 0
    End With

End Sub[/td]
[/tr]
[/table]

 

[EJB74]

mumps, thank you very much that worked!

 

[Fluff]

@Rick Rothstein
Thanks for that, unfortunately formulae like that are currently beyond my ken.
It's one of the many things that I need to learn up on.

 

[mumps]

You are very welcome.

 

[Rick Rothstein]

@Rick Rothstein
Thanks for that, unfortunately formulae like that are currently beyond my ken.
It's one of the many things that I need to learn up on.

The underlying concept is somewhat easy to grasp. The Evaluate function evaluates formula, most importantly, array formulas. There is, of course,, a little more to it than this, but basically you create a formula for a single cell and then change the single cell address to the address of a contiguous range. The argument to Evaluate is a text string, so you can concatenate all kinds of complex formulas together to get the Evaluate function to return an array which you can then assign to a cell range or use other VB code to manipulate as needed. Hopefully that brief introduction will get you started with them. As you try them out, you will run into stumbling blocks... PM me and I'll try to guide you through them if I can. I do not know everything, probably not even half, there is to know about the Evaluate function, but I seem to know enough to be able to cobble together some useful solutions from time to time. Evaluate is an extremely powerful function and would be a good one for you to add to your arsenal.