How to remove several ranges in table at the same time

[Brutusar]

Hi, I have a sheet with thousands or rows showing transport statuses. (See example below) . The start (Sendt) and end status (Delivered) is always the same, but the number of steps between these statuses will wary. The id number (Showing in col D will always be available for all steps in a shipment, and will be unique.

How can I find all shipments that are completed? (has both the Sendt and Delivered status so these can be removed from the list, including all steps between Sendt and Delivered? )

Example.xlsx
	A	B	C	D
1	1245	New York	Sendt	55468
2	1245	New York	Step 1	55468
3	1245	New York	Step 2	55468
4	1245	New York	Step 3	55468
5	1245	New York	Step 4	55468
6	1245	New York	Step 5	55468
7	1245	New York	Step 6	55468
8	1245	New York	Step 7	55468
9	1245	New York	Delivered	55468
10	1354	Chicago	Sendt	6657985
11	1354	Chicago	Step 2	6657985
12	1354	Chicago	Step 3	6657985
13	1354	Chicago	Step 4	6657985
14	1354	Chicago	Step 5	6657985
15	1445	Seattle	Sendt	5578223
16	1445	Seattle	Step 1	5578223
17	1445	Seattle	Step 2	5578223
18	1445	Seattle	Step 3	5578223
19	1445	Seattle	Step 4	5578223
20	1445	Seattle	Step 5	5578223
21	1445	Seattle	Step 6	5578223
22	1445	Seattle	Step 7	5578223
23	1445	Seattle	Delivered	5578223
24	5589	Dallas	Sendt	997855
25	5589	Dallas	Step 1	997855
26	5589	Dallas	Step 2	997855
Sheet1

 

[Brutusar]

Assuming the transport statuses to be deleted are the only ones that will have "Delivered" in Column C, give this code a try...

Sub DeleteDeliveredTrensports()
  Dim Cell As Range
  Columns("C").Replace "Delivered", "", xlWhole
  For Each Cell In Columns("C").SpecialCells(xlBlanks).Areas
    Columns("D").Replace Cell.Offset(, 1).Value, "#N/A", xlWhole
  Next
  Columns("D").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

Working perfect, thank you very much!

 

[jdellasala]

Sometimes...

 

[Rick Rothstein]

What do you mean by "sometimes"?

 

[jdellasala]

What do you mean by "sometimes"?

Sometimes VBA is the answer. But I still have a problem with anything that deletes original data instead of a copy whether it's VBA or a formula. Worth noting that it's impossible using Power Query since it only works on a copy of any data.

 

[Rick Rothstein]

Sometimes VBA is the answer. But I still have a problem with anything that deletes original data instead of a copy whether it's VBA or a formula. Worth noting that it's impossible using Power Query since it only works on a copy of any data.

If original data is to be preserved, then just have the code copy the original data to another location first, then run my original posted code (adjusted for the new location including modifying the last line) afterwards. For example...

Sub DeleteDeliveredTrensports()
  Dim Cell As Range
  Columns("A:D").Copy Range("F1")
  Columns("H").Replace "Delivered", "", xlWhole
  For Each Cell In Columns("H").SpecialCells(xlBlanks).Areas
    Columns("I").Replace Cell.Offset(, 1).Value, "#N/A", xlWhole
  Next
  Intersect(Columns("F:I"), Columns("I").SpecialCells(xlConstants, xlErrors).EntireRow).Delete
End Sub