# How to remove several ranges in table at the same time



## Brutusar (Dec 27, 2022)

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.xlsxABCD11245New YorkSendt5546821245New YorkStep 15546831245New YorkStep 25546841245New YorkStep 35546851245New YorkStep 45546861245New YorkStep 55546871245New YorkStep 6 5546881245New YorkStep 75546891245New YorkDelivered55468101354ChicagoSendt6657985111354ChicagoStep 26657985121354ChicagoStep 36657985131354ChicagoStep 46657985141354ChicagoStep 56657985151445SeattleSendt5578223161445SeattleStep 15578223171445SeattleStep 25578223181445SeattleStep 35578223191445SeattleStep 45578223201445SeattleStep 55578223211445SeattleStep 6 5578223221445SeattleStep 75578223231445SeattleDelivered5578223245589 DallasSendt997855255589 DallasStep 1997855265589 DallasStep 2997855Sheet1


----------



## Peter_SSs (Dec 27, 2022)

Is it correct that there is no heading row?


----------



## kvsrinivasamurthy (Dec 27, 2022)

Show the expected result for the given data.


----------



## jdellasala (Dec 27, 2022)

Added headers (fields) to your data and made it into an Excel Table:
Book1ABCD1CodeDestinationStatusCode221245New YorkSendt5546831245New YorkStep 15546841245New YorkStep 25546851245New YorkStep 35546861245New YorkStep 45546871245New YorkStep 55546881245New YorkStep 6 5546891245New YorkStep 755468101245New YorkDelivered55468111354ChicagoSendt6657985121354ChicagoStep 26657985131354ChicagoStep 36657985141354ChicagoStep 46657985151354ChicagoStep 56657985161445SeattleSendt5578223171445SeattleStep 15578223181445SeattleStep 25578223191445SeattleStep 35578223201445SeattleStep 45578223211445SeattleStep 55578223221445SeattleStep 6 5578223231445SeattleStep 75578223241445SeattleDelivered5578223255589 DallasSendt997855265589 DallasStep 1997855275589 DallasStep 2997855Sheet1
I actually started with a FILTER formula, but couldn't get it to work. Then saw that Power Query might be able to handle this easier, and sure enough it did!

```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupedRows = Table.Group(Source, {"Code", "Destination", "Code2"}, {{"Count", each _, type table [Code=number, Destination=text, Status=text, Code2=number]}}),
    AddedTableRowCount = Table.AddColumn(GroupedRows, "Custom", each Table.RowCount( [Count] )),
    AddedDeliveredTF = Table.AddColumn(AddedTableRowCount, "Custom.1", each [Count][Status]{[Custom]-1} = "Delivered"),
    FilteredTrueRows = Table.SelectRows(AddedDeliveredTF, each ([Custom.1] = false)),
    RemovedOtherColumns = Table.SelectColumns(FilteredTrueRows,{"Code", "Destination", "Code2", "Count"}),
    ExpandedCount = Table.ExpandTableColumn(RemovedOtherColumns, "Count", {"Status"}, {"Status"}),
    ReorderedColumns = Table.ReorderColumns(ExpandedCount,{"Code", "Destination", "Status", "Code2"}),
    ChangedType = Table.TransformColumnTypes(ReorderedColumns,{{"Code", Int64.Type}, {"Destination", type text}, {"Status", type text}, {"Code2", Int64.Type}})
in
    ChangedType
```
Which resulted in this table:
Book1FGHI1CodeDestinationStatusCode221354ChicagoSendt665798531354ChicagoStep 2665798541354ChicagoStep 3665798551354ChicagoStep 4665798561354ChicagoStep 5665798575589 DallasSendt99785585589 DallasStep 199785595589 DallasStep 2997855Sheet1
Maybe worth noting that when an entry (Code) is either changed to or added as Delivered (regardless of its position in the source table), or if a new code is added without a Delivered status, a Refresh of the resulting table will automatically add/remove the changed entries. Also, since the grouping is by Code, Destination, and Code2, any given Code must always have the same value for both Destination and Code2 which is what appeared in the original data.


----------



## Brutusar (Dec 27, 2022)

kvsrinivasamurthy said:


> Show the expected result for the given data.


Hi, this is how the result should be  (headings added)


----------



## Brutusar (Dec 27, 2022)

jdellasala said:


> Added headers (fields) to your data and made it into an Excel Table:
> Book1ABCD1CodeDestinationStatusCode221245New YorkSendt5546831245New YorkStep 15546841245New YorkStep 25546851245New YorkStep 35546861245New YorkStep 45546871245New YorkStep 55546881245New YorkStep 6 5546891245New YorkStep 755468101245New YorkDelivered55468111354ChicagoSendt6657985121354ChicagoStep 26657985131354ChicagoStep 36657985141354ChicagoStep 46657985151354ChicagoStep 56657985161445SeattleSendt5578223171445SeattleStep 15578223181445SeattleStep 25578223191445SeattleStep 35578223201445SeattleStep 45578223211445SeattleStep 55578223221445SeattleStep 6 5578223231445SeattleStep 75578223241445SeattleDelivered5578223255589 DallasSendt997855265589 DallasStep 1997855275589 DallasStep 2997855Sheet1
> I actually started with a FILTER formula, but couldn't get it to work. Then saw that Power Query might be able to handle this easier, and sure enough it did!
> 
> ...


Power query is good, but I am afraid I need VBA as it will be part of a longer process


----------



## jdellasala (Dec 27, 2022)

Brutusar said:


> Power query is good, but I am afraid I need VBA as it will be part of a longer process


The first spreadsheet app I used was VisiCalc which was released in 1979. I retired in 2020 after 27 years in IT Support and the last 5 years in Inventory Management of over 350,000 Smartphones at various stages of life, four carriers, and over 100,000 users, and replacement inventory for over 50 offices. It could take a day to fully analyze an Inventory Report against ONE carrier. Had I known Power Query, that process would have been reduced to the time to download the reports and then clicking Refresh All - less than an hour.
I don't understand what can only be described as a fetish for VBA which is an antiquated language, overly verbose, takes hours to write and debug, and easily broken. Power Query for all its shortcomings is intuitive, has a very helpful UI, and easy to learn. I'm just sorry I didn't learn it five years earlier! There are great YouTube playlists on the subject *here* and *here*. They are long, but you should be able to start being productive after just a few videos. And you can always get help here.
If VBA is your preferred solution, I'd have to withdraw.


----------



## Brutusar (Dec 27, 2022)

jdellasala said:


> The first spreadsheet app I used was VisiCalc which was released in 1979. I retired in 2020 after 27 years in IT Support and the last 5 years in Inventory Management of over 350,000 Smartphones at various stages of life, four carriers, and over 100,000 users, and replacement inventory for over 50 offices. It could take a day to fully analyze an Inventory Report against ONE carrier. Had I known Power Query, that process would have been reduced to the time to download the reports and then clicking Refresh All - less than an hour.
> I don't understand what can only be described as a fetish for VBA which is an antiquated language, overly verbose, takes hours to write and debug, and easily broken. Power Query for all its shortcomings is intuitive, has a very helpful UI, and easy to learn. I'm just sorry I didn't learn it five years earlier! There are great YouTube playlists on the subject *here* and *here*. They are long, but you should be able to start being productive after just a few videos. And you can always get help here.
> If VBA is your preferred solution, I'd have to withdraw.


Will have a look, thanks!


----------



## Rick Rothstein (Dec 27, 2022)

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
```


----------



## jdellasala (Dec 27, 2022)

Rick Rothstein said:


> 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()
> ...


Does this really delete the original data, or is my VBA that bad (TOTALLY POSSIBLE!)


----------



## Brutusar (Dec 27, 2022)

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.xlsxABCD11245New YorkSendt5546821245New YorkStep 15546831245New YorkStep 25546841245New YorkStep 35546851245New YorkStep 45546861245New YorkStep 55546871245New YorkStep 6 5546881245New YorkStep 75546891245New YorkDelivered55468101354ChicagoSendt6657985111354ChicagoStep 26657985121354ChicagoStep 36657985131354ChicagoStep 46657985141354ChicagoStep 56657985151445SeattleSendt5578223161445SeattleStep 15578223171445SeattleStep 25578223181445SeattleStep 35578223191445SeattleStep 45578223201445SeattleStep 55578223211445SeattleStep 6 5578223221445SeattleStep 75578223231445SeattleDelivered5578223245589 DallasSendt997855255589 DallasStep 1997855265589 DallasStep 2997855Sheet1


----------



## Brutusar (Dec 27, 2022)

Rick Rothstein said:


> 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()
> ...


Working perfect, thank you very much!


----------



## jdellasala (Dec 27, 2022)

Sometimes...


----------



## Rick Rothstein (Dec 27, 2022)

What do you mean by "sometimes"?


----------



## jdellasala (Dec 27, 2022)

Rick Rothstein said:


> 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 (Dec 27, 2022)

jdellasala said:


> 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
```


----------

