Deleting rows based on multiple different values....

andyeaston

New Member
Joined
Sep 20, 2016
Messages
16
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

Master OrderSlave Order NumberStatusType
5452515452510002
582390
582390

<tbody>
</tbody>
0001
584076

<tbody>
</tbody>
584076

<tbody>
</tbody>
0001
584076

<tbody>
</tbody>
584077

<tbody>
</tbody>
0001
584076

<tbody>
</tbody>
589016

<tbody>
</tbody>
0002
584076

<tbody>
</tbody>
589017

<tbody>
</tbody>
0001
584076

<tbody>
</tbody>
617664

<tbody>
</tbody>
0001
584076
632657

<tbody>
</tbody>
1401
585155
585155

<tbody>
</tbody>
0001
587638

<tbody>
</tbody>
587638

<tbody>
</tbody>
1202
587638
587742

<tbody>
</tbody>
0001

<tbody>
</tbody>


























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

Master OrderSlave Order NumberStatusType
582390

<tbody>
</tbody>
582390

<tbody>
</tbody>
2001
585155

<tbody>
</tbody>
585155

<tbody>
</tbody>
0001

<tbody>
</tbody>
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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.


MasterSlaveStatusType
6789016789010002
6789016789860000
6789017656780000
6789017654371200

<tbody>
</tbody>

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

Do this help?
 
Upvote 0
I assume the data starts in cell A2

Try this

Code:
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
 
Upvote 0
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.
Code:
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:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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