MATCH/ LOOK UP

[Miree]

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

 

[keiji kounoike]

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

 

[Stefi]

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: