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

Option Explicit


Sub delX()
    Dim lr As Long, i As Long
    lr = Range("B" & Rows.Count).End(xlUp).Row
    For i = lr To 1 Step -1
        If Not InStr(Range("B" & i), "/003") > 0 Then
            Range("B" & i).EntireRow.Delete
        End If
    Next i


End Sub

 

[Fluff]

Re: Need help with a delete & sort macro

Another option

Sub Buns1976()
   With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(right(@,3)=""003"",@,"""")", "@", .Address))
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With
End Sub

 

[Buns1976]

Re: Need help with a delete & sort macro

Thanks Fluff,

If I could trouble you for one more please?

Same scenario where UPC's are in Column B except we want to delete all rows except those that Begin with 02820000.

[TABLE="width: 143"]
<tbody>[TR]
[TD="class: xl63, width: 143"]028200003577/003[/TD]
[/TR]
</tbody>[/TABLE]


Thank you!

 

[Buns1976]

Re: Need help with a delete & sort macro

Fluff,

Never mind I got it.

Thanks so much for your help!

 

[Fluff]

Re: Need help with a delete & sort macro

Glad you figured it out & thanks for the feedback

 

[Buns1976]

Re: Need help with a delete & sort macro

Hi Fluff,

Ran into an obstacle with the macro. We are importing the data from another workbook and it is coming in as a table.
When I run the macro it deletes any cells in column B if they DO NOT end in 003 but fails to delete those rows.
Don't understand that as it works fine in a normal range of cells? Can you take a look at it please?

Thanks!

Sub COMPACT_PLU_IMPORT()    With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(right(@,3)=""003"",@,"""")", "@", .Address))
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With
End Sub

 

[Fluff]

Re: Need help with a delete & sort macro

Easiest option to to convert the table to a range prior to running the macro.

 

[Buns1976]

Re: Need help with a delete & sort macro

Hi Fluff,

Unfortunately when I convert to range I lose my connection to the import sheet. Not sure what the best way to go about this would be?

Thanks!

 

[Fluff]

Re: Need help with a delete & sort macro

I've never used linked/imported tables, so not sure what to suggest.
Especially as I don't know what impact deleting rows may have on your data.