Delete & sort macro

[Buns1976]

Hi Everyone,

I need to run a delete on a sheet based on criteria. Column B contains UPC's with approximately 1500 rows of Data
that look like what you see below. I want to delete every row EXCEPT the rows that have UPC's that END in 003 and then delete all empty rows.
Any help would be greatly appreciated!

Thank you!!

[TABLE="width: 134"]
<tbody>[TR]
[TD]028200009524/000[/TD]
[/TR]
[TR]
[TD]028200009524/003[/TD]
[/TR]
[TR]
[TD]028200009524/003[/TD]
[/TR]
[TR]
[TD]028200009524/003[/TD]
[/TR]
[TR]
[TD]028200009531/000[/TD]
[/TR]
[TR]
[TD]028200009531/000[/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

Re: Need help with a delete & sort macro

An alternative is to use Power Query/Get and Transform so long as you are running Excel 2010 or later. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Column1.2] = "000")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each [Column1.1]&"/"&[Column1.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.1", "Column1.2"})
in
    #"Removed Columns"

Translated, bring your table into PQ
Split the column of data using the "/' as your delimeter.
Filter out all unwanted rows
Concatenate the two columns adding back the delimeter.
Close and load to your workbook.