Match and delete

[topi1]

Need help with VBA. TY.

In the following example, I want the vba to loop through all column R cells where the column Q value is 9. Match the strings in those cells and see if they contain any of the strings in the column U. case insensitive match is fine. If the cell in the column R with column Q=9 does not contain any of the column U strings, delete it. Thank you. If the code works, last two cells should be deleted.

Book1
	Q	R	S	T	U
1		Theater:			delim1.
2		Movie:			delim2.
3					delim3.
4					delim4.
5		TITLE1			delim5.
6					delim6.
7	4	Eros: Dunn			delim7.
8	4	Strand shows Abba.			delim8.
9	4	Regal: Superman			delim9.
10	4	Roxi shows Cars.			delim10.
11	4	Metro: Batman
12	4	Minerva: Starwars
13
14
15
16	5	Eros shows Superman nextweek.
17	5	Metro shows Dunn next month.
18	5
19		TITLE2
20	9	Eros shows Superman nextweek. Delim4.
21	9	Metro shows Dunn next month. Delim3. Closed today.
22	9	Delim2. Minerva is showing Abba.
23	9	Before Metro Roxi shows Cars.
24	9	After Eros Strand shows Abba.
Sheet2

 

[Cubist]

Try on a copy.

Sub CheckAndClear()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim dataQ As Variant, dataR As Variant, dataU As Variant
    Dim wordFound As Boolean

    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change Sheet1 to your sheet name

    lastRow = ws.Cells(ws.Rows.Count, "Q").End(xlUp).Row

    dataQ = ws.Range("Q1:Q" & lastRow).Value
    dataR = ws.Range("R1:R" & lastRow).Value
    dataU = ws.Range("U1:U" & ws.Cells(ws.Rows.Count, "U").End(xlUp).Row).Value

    For i = 1 To UBound(dataQ, 1)
        If dataQ(i, 1) = 9 Then
            wordFound = False
            For j = 1 To UBound(dataU, 1)
                If InStr(1, dataR(i, 1), dataU(j, 1), vbTextCompare) > 0 Then
                    wordFound = True
                    Exit For
                End If
            Next j
            If Not wordFound Then
                ws.Range("Q" & i).Resize(1, 2).ClearContents
            End If
        End If
    Next i
End Sub

 

[topi1]

Thank you thank you. Brilliant. Very helpful.

 

[Cubist]

You're welcome.

 

[topi1]

If I can burden you with two more vbas/steps. Greatly apprecitae it in advance for you considering.

match column R cells with column U cells when the column Q value is 9.
Partial match and case-insensitive allowed.
Sort only these column R cells from top to bottom based on the matched strings with the column U and order of those strings in the column U
example as follows.
AS you can see delim2. is followed by delim3. and then delim4. after it has been sorted; as per their order in the column U.

9 Delim2. Minerva is showing Abba.
9 Metro shows Dunn next month. Delim3. Closed today.
9 Eros shows Superman nextweek. Delim4.


Then...

Match column R cells with column U cells when column Q value is 9. Partial match and case insensitive allowed.
Delete the part of the string which matches leaving behind anything to the right and left.
example follows.

9 Minerva is showing Abba.
9 Metro shows Dunn next month. Closed today.
9 Eros shows Superman nextweek.

 

[Cubist]

Is the block of 9 always continuous?

 

[topi1]

No. TY>

 

[Cubist]

If so, can you provide an example of the most complex case you might encounter?

 

[topi1]

But it can be made contiguous if necessary. In fact all blanks in the column R can be removed. It won't affect anything. I have been having problems with vba to remove blanks. The one that woks most consistently I delete the cells with LEN<1.

 

[Cubist]

When you say delete, do you mean delete the entire row or the content?