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

[TABLE="width: 283"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]ADDRESS[/TD]
[TD] AGE[/TD]
[TD] COMPANY[/TD]
[TD]POSITION[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]DDD[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]GASDD[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

in "Need to be removed" sheet
[TABLE="width: 50"]
<tbody>[TR]
[TD]NAME[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]DDD[/TD]
[/TR]
[TR]
[TD]XYZ

[/TD]
[/TR]
</tbody>[/TABLE]

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