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

justin

New Member
Joined
Apr 3, 2002
Messages
5
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top