Deleting rows based on multiple different values....

[andyeaston]

I have a sheet of about 1000 rows ...

So there are 11 rows of Master Order numbers but only 5 different Master Orders. Some Master orders have Slave orders, some do not. If a Master order has a Status of 12, 13 or 14 or a Type of 02 I need to remove that row. The problem Im having is removing rows when just one of the Slave orders has a Status of 12, 13, or 14 or a Type of 02 I need to remove every instance of that Master order.


Ive been working on this for a few days and the problem is when I have more than 2 Slave orders I run into trouble.

If I need to explain further, please let me know.

Thank you

below is an example of what I start with

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Master Order[/TD]
[TD]Slave Order Number[/TD]
[TD]Status[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]545251[/TD]
[TD]545251[/TD]
[TD]00[/TD]
[TD]02[/TD]
[/TR]
[TR]
[TD]582390[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 122"]582390[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 128"]584076[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 122"]584076[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 128"]584076[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 122"]584077[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 128"]584076[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 122"]589016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00[/TD]
[TD]02[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 128"]584076[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 122"]589017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 128"]584076[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 122"]617664[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]584076[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 122"]632657[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]14[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]585155[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 122"]585155[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 128"]587638[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 122"]587638[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12[/TD]
[TD]02[/TD]
[/TR]
[TR]
[TD]587638[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 122"]587742[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


























below is an example of the desired result....

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Master Order[/TD]
[TD]Slave Order Number[/TD]
[TD]Status[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl66, width: 128"]582390[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl66, width: 128"]582390[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl66, width: 128"]585155[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl66, width: 128"]585155[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00[/TD]
[TD]01[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

I am a little confused.
Only the Status and Type columns should be reviewed.


Or do you also have to check if it is a master order or a slave order?
You could explain this with any of your examples.

 

[andyeaston]

I am a little confused.
Only the Status and Type columns should be reviewed.


Or do you also have to check if it is a master order or a slave order?
You could explain this with any of your examples.

So the only conditions are a 12, 13 or 14 in the Status Column or an 02 in the Type Column. If one of those conditions exist I need to remove all of rows with the same Master order.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Master[/TD]
[TD]Slave[/TD]
[TD]Status[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]678901[/TD]
[TD]678901[/TD]
[TD]00[/TD]
[TD]02[/TD]
[/TR]
[TR]
[TD]678901[/TD]
[TD]678986[/TD]
[TD]00[/TD]
[TD]00[/TD]
[/TR]
[TR]
[TD]678901[/TD]
[TD]765678[/TD]
[TD]00[/TD]
[TD]00[/TD]
[/TR]
[TR]
[TD]678901[/TD]
[TD]765437[/TD]
[TD]12[/TD]
[TD]00[/TD]
[/TR]
</tbody>[/TABLE]

In this example I would need to remove all rows...not just the ones with an 12 or 02

Do this help?

 

[DanteAmor]

I assume the data starts in cell A2

Try this

Sub Deleting_rows()
  Dim lr As Long, i As Long, a, r As Range, exists As Boolean, dict As Object
  Application.ScreenUpdating = False
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Set r = Range("A" & lr + 1)
  Set dict = CreateObject("scripting.dictionary")
  a = Range("A2:D" & lr)
  For i = 1 To UBound(a)
    Select Case True
      Case a(i, 3) = 12, a(i, 3) = 13, a(i, 3) = 14, Val(a(i, 4)) = 2
        dict(a(i, 1)) = dict(a(i, 1))
    End Select
  Next i
  For i = 1 To UBound(a)
    If dict.exists(a(i, 1)) Then Set r = Union(r, Range("A" & i + 1))
  Next i
  r.EntireRow.Delete
  Set r = Nothing: Erase a
  Application.ScreenUpdating = True
End Sub

 

[andyeaston]

Works absolutely beautifully! Thanks so much!!!!!!!!!!


I need to learn about UBound and LBound.

 

[DanteAmor]

Works absolutely beautifully! Thanks so much!!!!!!!!!!
I need to learn about UBound and LBound.

I'm glad to help you. Thanks for the feedback.

LBound function, Returns a Long containing the smallest available subscript for the indicated dimension of an array.

UBound function, Returns the upper limit of an array dimension.

Eg.

Sub test()
  Dim arr, low As Long, upp As Long
  arr = Array("a", "b", "c")  'In this array the counter starts at [B][COLOR=#0000ff]0[/COLOR][/B]
  low = LBound(arr)           'return 0
  upp = UBound(arr)           'return 2
End Sub

In this case:

Sub test2()
  Dim arr, low As Long, upp As Long
  arr = Range("A1:A3")  'In this array the counter starts at [B][COLOR=#0000ff]1[/COLOR][/B]
  low = LBound(arr)     'return 1
  upp = UBound(arr)     'return 3
End Sub

 

[andyeaston]

Thank you!

I will work with that!

 

[DanteAmor]

Youre welcome.