How to remove rows from list in excel by vba

[m_vishal_c]

HI i have two sheet in one workbook, " Sheet1" and " Need to be removed ". i need to remove all data from sheet1 from matching "Need to be removed " sheet column.

For example in Sheet1

NAME	ADDRESS	AGE	COMPANY	POSITION
ABC	1	1	1	1
XYZ	2	1	2	1
ABC	3	11	3	11
DDD	4	1	4	1
GASDD	4	1	4	1
XYZ	5	1	5	1

<tbody>
</tbody>

in "Need to be removed" sheet

NAME

ABC

DDD

XYZ

<tbody>
</tbody>

I need to remove all rows from Sheet 1 where name=abc from "Need to be removed" list.

for that i am using below code but it does not remove all rows. Please guide me

Dim iListCount As IntegerDim iCtr As Integer




' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False




' Get count of records to search through (list that will be deleted).
iListCount = Sheets("Sheet1").Range("A1:A5000").Rows.Count




' Loop through the "master" list.
For Each x In Sheets("Need to be removed").Range("A2:A30")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet1").Cells(iCtr, 1).[COLOR=#333333]EntireRow.Delete xlShiftUp[/COLOR]
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"

Please guide me. much appreciate your support and time
thanks

 

[alansidman]

Maybe this:

Option Explicit


Sub Remove()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim i As Long, j As Long
    Dim lr1 As Long, lr2
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Need to be removed")
    lr1 = s1.Range("A" & Rows.Count).End(xlUp).Row
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 2 To lr2
        For j = lr1 To 2 Step -1
            If s1.Range("A" & j) = s2.Range("A" & i) Then
                s1.Range("A" & j).EntireRow.Delete
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub