[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Macro to delete specific rows

Gert-Jan

12/20/2006 8:03:00 AM

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


2 Answers

Gert-Jan

12/20/2006 9:26:00 AM

0

Hi Martin,

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

Regards, Gert-Jan

"Martin Fishlock" <martin_fishlock@yahoo.co.uk.cutthis> schreef in bericht
news:E9FEADE6-221C-4467-9E10-57A9A7A1A903@microsoft.com...
> 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
>>
>>
>>


Gert-Jan

12/20/2006 9:30:00 AM

0

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" <martin_fishlock@yahoo.co.uk.cutthis> schreef in bericht
news:E9FEADE6-221C-4467-9E10-57A9A7A1A903@microsoft.com...
> 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
>>
>>
>>