I have two sheets, In sheet 1 column A is full of numbers, I want to delete
all the lines in sheet 2 where column B does not match any number in Sheet 1
column A.

I am currently using the following code for another aapplication, if you
could modify this it would help me understand what I am doing a little better

Dim rng As Range
Dim i As Long

Set rng = ActiveSheet.Range(Cells(1, "DL"), Cells(Rows.Count, "DL").End(xlUp))

'Work backwards from bottom to top when deleting rows

With rng
For i = .Rows.Count To 1 Step -1
If Not Trim(.Cells(i)) = Trim(UserForm7.TextBox1) Then
.Cells(i).EntireRow.Delete
End If

Next i
End With

Thank you

I'm not sure if this would work in your case, but try this one.

Sub Macrotest()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim rng As Range, r As Range, c As Range

Set sh1 = Worksheets("sheet1") '<<==Change to your name
Set sh2 = Worksheets("sheet2") '<<==Change to your name
Set rng1 = Range(sh1.Cells(1, "A"), _
sh1.Cells(Rows.Count, "A").End(xlUp))
Set rng2 = Range(sh2.Cells(1, "B"), _
sh2.Cells(Rows.Count, "B").End(xlUp))

On Error Resume Next
For Each r In rng1
Set c = rng2.Find(r.Value, LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=False)
Do While (Not c Is Nothing)
c.EntireRow.Delete
Set c = rng2.Find(r.Value, LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=False)
Loop
Next
End Sub

Keiji

Miree wrote:
I have two sheets, In sheet 1 column A is full of numbers, I want to delete
all the lines in sheet 2 where column B does not match any number in Sheet 1
column A.

I am currently using the following code for another aapplication, if you
could modify this it would help me understand what I am doing a little better

Dim rng As Range
Dim i As Long

Set rng = ActiveSheet.Range(Cells(1, "DL"), Cells(Rows.Count, "DL").End(xlUp))

'Work backwards from bottom to top when deleting rows

With rng
For i = .Rows.Count To 1 Step -1
If Not Trim(.Cells(i)) = Trim(UserForm7.TextBox1) Then
.Cells(i).EntireRow.Delete
End If

Next i
End With

Thank you

Sub test()
Dim rng As Range
Dim i As Long
Dim rngend As Long
Worksheets("Sheet2").Activate
rngend = Cells(Rows.Count, "B").End(xlUp).Row
Set rng = ActiveSheet.Range(Cells(1, "B"), Cells(rngend, "B"))

'Work backwards from bottom to top when deleting rows

With rng
For i = rngend To 1 Step -1
hit = 0
On Error Resume Next
hit = WorksheetFunction.Match(.Cells(i),
Worksheets("Sheet1").Columns("A"), 0)
On Error GoTo 0
If hit = 0 Then .Cells(i).EntireRow.Delete
Next i
End With

End Sub

Regards,
Stefi

€žMiree€� ezt Ã*rta:

I have two sheets, In sheet 1 column A is full of numbers, I want to delete
all the lines in sheet 2 where column B does not match any number in Sheet 1
column A.

I am currently using the following code for another aapplication, if you
could modify this it would help me understand what I am doing a little better

Dim rng As Range
Dim i As Long

Set rng = ActiveSheet.Range(Cells(1, "DL"), Cells(Rows.Count, "DL").End(xlUp))

'Work backwards from bottom to top when deleting rows

With rng
For i = .Rows.Count To 1 Step -1
If Not Trim(.Cells(i)) = Trim(UserForm7.TextBox1) Then
.Cells(i).EntireRow.Delete
End If

Next i
End With

Thank you