Selecting and deleting records

[Guest]

In my spreadsheet I have pairs of records identified by having an identical
"id number" as one of the fields. Another field in one of the pair indicates
whether this pair should be deleted or not.

I want to identify which records have the "delete" indicator in the delete
indicator field and then delete the both records that are paired by having
the similar "id numbers".

Can anyone suggest a way to do this?

Thanks.

 

[Ron de Bruin]

Hi bbig80524

So one record of the pair have the "delete" indicator in a field
Is that the first record with the same ID number

Start here and post back if you need more help
http://www.rondebruin.nl/delete.htm

 

[Guest]

Hi Ron,

I'm not sure I understand your question. I can sort the spreadsheet on the
ID number. Doing this puts the pairs side by side. One of the records has the
delete indicator, the other does not. Are you asking, of the pair is the
record with the delete indicator the first record or the second record? If
this is the question, the delete indicator is in the second of the record
pairs.

 

[Guest]

big,

Option Explicit
Dim MyRange As Range
Sub DeletePairs()
Dim rng1 As Range, rng2 As Range
Dim first As String
With MyRange
Set rng1 = .Find(what:="delete", searchorder:=xlByColumns)
If Not rng1 Is Nothing Then
Set rng2 = .Find(what:=Cells(rng1.Row, XXX), searchorder:=xlByColumns)
If Not rng2 Is Nothing Then
first = rng2.Address
Do
Set rng2 = .FindNext(rng2)
Loop While rng1.Row = rng2.Row And rng2.Address <> first
If rng1.Row <> rng2.Row Then
rng1.EntireRow.Delete
rng2.EntireRow.Delete
End If
End If
DeletePairs
End If
End With
End Sub

NOTE: MyRange must include the "Delete" indicator column and the ID column.
Change the 'XXX' in the code to the column number that corresponds to the ID
column. (ie. A = 1, B = 2)

Mike

 

[Ron de Bruin]

OK

What do you mean by delete indicator ?
The word "delete"

In this tester it search for the word delete in column C (C2:C100) and delete the row and the row above it

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 2
EndRow = 100

For Lrow = EndRow To StartRow Step -1

If IsError(.Cells(Lrow, "C").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "C").Value = "delete" Then .Rows(Lrow - 1 & ":" & Lrow).Delete

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub