Macro to delete specific rows
 

Hi,

In sheet1, range A1:P40 I have values. In the range A1:A40 there are ID
numbers. In sheet2, range (A1:A10) there are also ID numbers, wich might
correspondend to the range A1:A40 in Sheet1. I want a macro that deletes the
rows in sheet1 with the ID-numbers that are in sheet2.

Any help would be appriciated.

Beste regards, Gert-Jan

Macro to delete specific rows
 

Hi Martin,

Thanks, but it doesn't work. When I remove the error line, I will get an
error.

Regards, Gert-Jan

"Martin Fishlock" schreef in bericht
...
Hi Gert-Jan,

Try this one:

Option Explicit

Sub delrows()

Dim lData As Long
Dim ans As Variant
On Error Resume Next
For lData = 40 To 1 Step -1
ans = -1
ans = Application.WorksheetFunction.Match( _
Worksheets("sheet1").wsData.Cells(lData, 1), _
Worksheets("sheet2").Range("A1:A10"), 0)
If ans < -1 Then wsData.Rows(lData).Delete
Next lData
End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Gert-Jan" wrote:

Hi,

In sheet1, range A1:P40 I have values. In the range A1:A40 there are ID
numbers. In sheet2, range (A1:A10) there are also ID numbers, wich might
correspondend to the range A1:A40 in Sheet1. I want a macro that deletes
the
rows in sheet1 with the ID-numbers that are in sheet2.

Any help would be appriciated.

Beste regards, Gert-Jan

Macro to delete specific rows
 

This works fine:

Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer
Application.ScreenUpdating = False
iListCount = Sheets("sheet1").Range("A1:A100").Rows.Count
For Each x In Sheets("Sheet2").Range("A1:A100")
For iCtr = 1 To iListCount
If x.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
Sheets("Sheet1").Cells(iCtr, 1).Delete xlShiftUp
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
End Sub


"Martin Fishlock" schreef in bericht
...
Hi Gert-Jan,

Try this one:

Option Explicit

Sub delrows()

Dim lData As Long
Dim ans As Variant
On Error Resume Next
For lData = 40 To 1 Step -1
ans = -1
ans = Application.WorksheetFunction.Match( _
Worksheets("sheet1").wsData.Cells(lData, 1), _
Worksheets("sheet2").Range("A1:A10"), 0)
If ans < -1 Then wsData.Rows(lData).Delete
Next lData
End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Gert-Jan" wrote:

Hi,

In sheet1, range A1:P40 I have values. In the range A1:A40 there are ID
numbers. In sheet2, range (A1:A10) there are also ID numbers, wich might
correspondend to the range A1:A40 in Sheet1. I want a macro that deletes
the
rows in sheet1 with the ID-numbers that are in sheet2.

Any help would be appriciated.

Beste regards, Gert-Jan