Delete Rows based on not existing in range

[mralmackay]

After some help on this, can find examples on the forums where row
should be deleted based on X value. However, my requirement is:

Within Sheet1 contains x thousand rows, with data populated in up to
10 columns for each row. Based on Column C I need to delete any rows
within Sheet1 where the name in Column C is NOT EQUAL to a list of
names in Sheet2 Range Valid

For example

Sheet1
A: B: C: D: E:
123 Test Al Test Test
124 Test Mike Test Test
421 Test John Test Test
152 Test Al Test Test
242 Test Sarah Test Test
142 Test Tom Test Test


Sheet2
A:
Al
Mike
Tom

So in the above example Sarah and John don't appear within Sheet2's
range so I would want these lines deleting.

Appreciate any help as always.

Regards, Al.

 

[Joel]

Sub delrows()

Worksheets("sheet2").Activate
Sh2LastRow = Cells(Rows.Count, "A"). _
End(xlUp).Row
Set sh2names = Worksheets("sheet2"). _
Range(Cells(1, "A"), _
Cells(Sh2LastRow, "A"))

Worksheets("sheet1").Activate
Sh1LastRow = Cells(Rows.Count, "C"). _
End(xlUp).Row

RowCount = 1
Do While Not IsEmpty(Cells(RowCount, "C"))
MyName = Cells(RowCount, "C")
Set c = sh2names.Find(MyName, LookIn:=xlValues)

If c Is Nothing Then

Cells(RowCount, "C").EntireRow.Delete
Else
RowCount = RowCount + 1
End If

Loop


End Sub

 

[Bernie Deitrick]

Al,

Try the macro below. This assumes that the list of valid values is actually a named range Valid

HTH,
Bernie
MS Excel MVP

Sub RemoveNotValid()
Dim myRow As Long
With Worksheets("Sheet1")
myRow = .Cells(.Rows.Count, 3).End(xlUp).Row
.Range("D1").EntireColumn.Insert Shift:=xlToRight
.Range("D1" & myRow).FormulaR1C1 = "=ISERROR(MATCH(RC[-1],Valid,FALSE))"
.Range("D1" & myRow).AutoFilter Field:=1, Criteria1:="TRUE"
.Range("D2" & myRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Range("D2" & myRow).AutoFilter
.Range("D2").EntireColumn.Delete
End With
End Sub

 

[mralmackay]

Thanks to you both for your help on this. Really appreciated.

Cheers, Al.

Al,

Try the macro below. This assumes that the list of valid values is actually a named range Valid

HTH,
Bernie
MS Excel MVP

Sub RemoveNotValid()
Dim myRow As Long
With Worksheets("Sheet1")
myRow = .Cells(.Rows.Count, 3).End(xlUp).Row
.Range("D1").EntireColumn.Insert Shift:=xlToRight
.Range("D1" & myRow).FormulaR1C1 = "=ISERROR(MATCH(RC[-1],Valid,FALSE))"
.Range("D1" & myRow).AutoFilter Field:=1, Criteria1:="TRUE"
.Range("D2" & myRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Range("D2" & myRow).AutoFilter
.Range("D2").EntireColumn.Delete
End With
End Sub

After some help on this, can find examples on the forums where row
should be deleted based on X value. However, my requirement is:

Within Sheet1 contains x thousand rows, with data populated in up to
10 columns for each row. Based on Column C I need to delete any rows
within Sheet1 where the name in Column C is NOT EQUAL to a list of
names in Sheet2 Range Valid

For example

Sheet1
A: B: C: D: E:
123 TestAlTest Test
124 Test Mike Test Test
421 Test John Test Test
152 TestAlTest Test
242 Test Sarah Test Test
142 Test Tom Test Test

So in the above example Sarah and John don't appear within Sheet2's
range so I would want these lines deleting.

Appreciate any help as always.

Regards,Al.- Hide quoted text -

- Show quoted text -