Match and delete

T

topi1

Board Regular
Joined
Aug 6, 2014
Messages
190
Office Version
  1. 2010
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
QRSTU
1Theater:delim1.
2Movie:delim2.
3delim3.
4delim4.
5TITLE1delim5.
6delim6.
74Eros: Dunndelim7.
84Strand shows Abba.delim8.
94Regal: Supermandelim9.
104Roxi shows Cars.delim10.
114Metro: Batman
124Minerva: Starwars
13
14
15
165Eros shows Superman nextweek.
175Metro shows Dunn next month.
185
19TITLE2
209Eros shows Superman nextweek. Delim4.
219Metro shows Dunn next month. Delim3. Closed today.
229Delim2. Minerva is showing Abba.
239Before Metro Roxi shows Cars.
249After Eros Strand shows Abba.
Sheet2
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Try on a copy.
VBA Code: 
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
 
Upvote 0
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.
 
Upvote 0
If so, can you provide an example of the most complex case you might encounter?
 
Upvote 0
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.
 
Upvote 0
When you say delete, do you mean delete the entire row or the content?
 
Upvote 0
You must log in or register to reply here.

Similar threads

zookeepertx
  • Question
Having trouble "deciphering" some code so I can see how it works
Replies
7
Views
167
zookeepertx
zookeepertx
rjplante
  • Solved
Loop Count Math issue
Replies
6
Views
90
Akuini
Akuini
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
430
Gajendran Yadhav
G
I
  • Solved
Using InStr to Find Matches Between Columns
Replies
1
Views
616
Farscape2020
F
B
  • Solved
Find column and delete rows below....
2
Replies
11
Views
669
Alex Blakenburg
A

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top