eliminating a bunch of rows based on the value of a single r

[justin]

cells example: a spreadsheet with 2 columns (id and flag) only.
id flag
a 1
b 1
c 2
c 3
c 10
c 10
d 1
d 2
d 6
d 12
e 1
e 7
e 8
f 1

the task is to find the ids that contain a flag greater than 11 and remove all of the rows with those ids. In this case id=d would be removed 5 rows.

[UHsoccer]

On Page 12 I found this solution
to hide all rows that contain "Hide" in Col A
Also created the opposite (False) to UNhide

Sub HideRows()
Dim cell As Range
For Each cell In Range("A:A")
If cell.Value = "HIDE" Then
cell.EntireRow.Hidden = True
End If
Next
End Sub

[justin]

BUt I needed all the rows for the offensive id removed. in this case "d".

I got it working thanks.

Now I need the same logic but for "Flags out of order". IE. if the flags are not in ascending order then the id will be deleted.

Believe this is for a real application. I will explain it if you need.

[justin]

OK here is the data
id flag
a 1
a 2
a 3
b 1
b 3
b 2

I want the macro to figure out that "b"s flags are out of order so it should delete all 3 of b's rows.

[justin]

I got it . on to next problem.
Thanks

[Derek]

Justin

To remove all rows of a given letter where the numbers against that letter are out of sequence.
If your letters are in A2:A1000 and the numbers in B2 to B1000 (ie row 1 is blank) you could use this macro. (it temporarily uses columns UV and IV)

Application.ScreenUpdating = False
Range("IU2").FormulaR1C1 = _
"=IF(RC[-254]=R[1]C,RC[-254],IF(AND(R[1]C[-253]<=RC[-253],RC[-254]=R[1]C[-254]),RC[-254],0))"
Range("IV2").FormulaR1C1 = _
"=IF(AND(RC[-255]<>R[-1]C[-255],RC[-255]<>RC[-1]),0,IF(RC[-255]=RC[-1],RC[-1],IF(RC[-255]=R[-1]C[-1],R[-1]C[-1],R[-1]C)))"
Range("IU2:IV2").AutoFill Destination:=Range("IU2:IV1000"), Type:=xlFillDefault
For Each cell In Range("IU1:IV1000")
cell.Formula = cell.Value
Next
Dim i As Long
For i = Cells(Rows.Count, "IV").End(xlUp).Row To 1 Step -1
If Cells(i, "IV").Value <> 0 Then Cells(i, "IV").EntireRow.Delete
Next i
Range("IU1:IV1000").ClearContents
End Sub

regards
Derek


[ This Message was edited by: Derek on 2002-04-06 01:49 ]

[ This Message was edited by: Derek on 2002-04-06 21:48 ]