How to delete entire rows if value match another list -help!

cgquinta

New Member
Joined
Sep 11, 2014
Messages
1
What I have is (7) worksheets with a list of values in column b5 thru b558 in each one and in another worksheet I have another list of values in column w5 thru w128. What I need to do is delete any rows from the b5 thru b558 set that contain a value from the w5-w128 set, any thoughts on how to do this? If you want me to attach the spreadsheet's I will just let me know. I'm not very familiar with excel functions so your patience is greatly appreciated.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

PC_Meister

Board Regular
Joined
Aug 28, 2013
Messages
72
What I have is (7) worksheets with a list of values in column b5 thru b558 in each one and in another worksheet I have another list of values in column w5 thru w128. What I need to do is delete any rows from the b5 thru b558 set that contain a value from the w5-w128 set, any thoughts on how to do this? If you want me to attach the spreadsheet's I will just let me know. I'm not very familiar with excel functions so your patience is greatly appreciated.

I would highlight the cells first instead of deleting them (it makes the code a little simpler). You can then filter by color and delete. Try this
Code:
Sub Highlight4Deletion()
    Dim srcWS As Worksheet ' source worksheet
    Set srcWS = ThisWorkbook.Worksheets("NAME OF YOUR SHEET HERE")
    Dim trgWS As Worksheet ' target worksheet
    Set trgWS = ThisWorkbook.Worksheets("NAME OF YOUR SHEET HERE")
    
    Dim i As Long, j As Long
    
    For i = 5 To 558
        For j = 5 To 128
            If trgWS.Range("B" & i).Value = srcWS.Range("W" & j).Value Then
                trgWS.Range("B" & i).Interior.ColorIndex = 4
                Exit For
            End If
        Next j
    Next i
    
    Set srcWS = Nothing
    Set trgWS = Nothing
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,718
Messages
5,833,289
Members
430,202
Latest member
Faizal5zl

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
Top