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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

PC_Meister

Board Regular
Joined
Aug 28, 2013
Messages
71
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,187
Members
409,749
Latest member
BorisYeltsin

This Week's Hot Topics

Top