Multiple Row Deletion

[johnnyb5]

I've inserted a macro to identify the shipping code in column "B" and if the code is "MRO" Or "RCP" Or "SHP" then it should delete the entire row. The macro is working fine however, the circumstances make it inadequate. I need help in identifying all the repetitions of the part# listed in column "A" that either of the 3 shipping codes would identify and delete all of those rows.

Example: One part number (X123) in column A could be listed several times in that column and the corresponding shipping status in column B would change perhaps six to ten times prior to its arrival. If one of those codes in column B is MRO, RCP, or SHP; then I want every occurrence of that part#'s row deleted.

My current macro only deletes a specific occurrence and is listed below:

Dim Last As Long
Last = Cells(Rows.Count, "E").End(xlUp).Row
For i = Last To 1 Step -1
If Cells(i, "B").Value = "MRO" Or "RCP" Or "SHP" Then
Cells(i, "B").EntireRow.Delete
End If
Next I

Could anyone assist with modifying the macro to find every occurrence of the part number in column A and delete the entire row??

Thanks JB

 

[Peter_SSs]

Rather than us guess exactly what your data is like, could we have 10-15 rows of varied sample data, some of which have these multiple rows and some don't, then give your explanation in relation to that sample data?

Edit: Also ..
1. Are the values in columns A:B the result of formulas or just 'constant' values?

2. Do you have a fixed number of columns altogether? If so, what are they?

 

[johnnyb5]

My apologies for not being clear. Looking below you can notice that MRO, SHP, and RCP occur in Column B. The part# in column A that correspond to those occurrences are A234 and C456. Therefore I'd like the Macro to delete every row (entire row) with an occurrence of A234 and every occurrence of C456 (even if other occurrences are different statuses). In the example below, only B345 rows should be remaining.

Product Status
A234 PD
B345 PD
C456 PD
A234 TRN
A234 MRO
C456 TRN
C456 MRO
B345 TRN
A234 SHP
A234 RCP

 

[johnnyb5]

Let me answer the other questions as well, The data is downloaded from a web-utility so that in this state it is just text basically. Hundreds to thousands of rows however (slightly over 15k was my largest download I believe). The columns do vary based on circumstances from 5 columns wide to 80 columns (A:CB).

 

[Peter_SSs]

Edit: OK, seen you other post now.

 

[Peter_SSs]

Test this in a copy of your workbook.

Sub DelRws()
  Dim a, b
  Dim d As Object
  Dim i As Long, nc As Long
  
  Set d = CreateObject("scripting.dictionary")
  d.CompareMode = 1
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  For i = 1 To UBound(a)
    Select Case a(i, 2)
      Case "MRO", "RCP", "SHP"
        d(a(i, 1)) = 1
    End Select
  Next i
  For i = 1 To UBound(a)
    If d.exists(a(i, 1)) Then b(i, 1) = 1
  Next i
  Application.ScreenUpdating = False
  With Range("A2").Resize(UBound(a), nc)
    .Columns(nc).Value = b
    .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
      MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    On Error Resume Next
    .Columns(nc).SpecialCells(xlConstants).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

 

[johnnyb5]

Peter,
Excellent, and I think that's at least twice that you've saved my day!!

Sincerely,
JB

 

[Peter_SSs]

Peter,
Excellent, and I think that's at least twice that you've saved my day!!

Sincerely,
JB

Glad it worked for you.