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:

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,271
Office Version
2007
Platform
Windows
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

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,271
Office Version
2007
Platform
Windows
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
 

andyeaston

New Member
Joined
Sep 20, 2016
Messages
16
Works absolutely beautifully! Thanks so much!!!!!!!!!!


I need to learn about UBound and LBound.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,271
Office Version
2007
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,090,459
Messages
5,414,647
Members
403,540
Latest member
mmorejon1215

This Week's Hot Topics

Top