Find words using wildcards and delete any rows not containing certain letters

[ceebeedee]

Hi

Apologies if the title seems a little hazy.

I am trying to create a macro that will search for all my product codes starting with Z but have other combinations of letters after it, new ones are added but I am not necessarily informed of this.

I need the macro to keep all the Z codes and delete all the remainder, all the codes are in the A column.

I believe it needs the use of a wildcard and hopefully will be dynamic (will 'see' any new codes that are added)

Any help would be greatly appreciated.

Thanks in advance

Chris

 

[Fluff]

How about

Sub Filterdelete()

   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1").AutoFilter 1, "<>Z*"
      .UsedRange.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      .AutoFilterMode = False
   End With
   
End Sub

 

[ceebeedee]

Hi Fluff

Many thanks for the code, local help is always greatly appreciated .

I need to tweak this though, I was hoping that this piece of code would work with the array list that I already have within the macro to keep certain codes that don't begin with a Z, is there any solution to keeping both or amalgamating them? the array list is List = Array("QAPCCS", "QACAT", "PBPPPS", "AEPMTBAGPMF", "ZACOPPMAGFP", "QAFCCM", "QABRMP", "PPMBBCFP", "PPMBBCF", "PPMBBCP", "QAISOFOU", "QAPCCS", "QACGDPRP", "ZCSAGFP", "PMPCMFP", "PMPCMF", "PMPCMP", "PBPPS", "PPMAGFP", "PPMAGF", "PPMAGP", "SDI-SDA", "SDI-SDAEX", "SDI-SDM", "PPMAGPGF", "QAISOP", "QAISO20KF", "QAISO20KP", "PBPMBFP", "PBPMBF", "PBPMBPP", "QASCATP", "QACBRM", "QACOBITF", "PPMPPCFP")

Many thanks in advance for any help you can offer.

Chris

 

[Rick Rothstein]

Hi Fluff

Many thanks for the code, local help is always greatly appreciated .

I need to tweak this though, I was hoping that this piece of code would work with the array list that I already have within the macro to keep certain codes that don't begin with a Z, is there any solution to keeping both or amalgamating them? the array list is List = Array("QAPCCS", "QACAT", "PBPPPS", "AEPMTBAGPMF", "ZACOPPMAGFP", "QAFCCM", "QABRMP", "PPMBBCFP", "PPMBBCF", "PPMBBCP", "QAISOFOU", "QAPCCS", "QACGDPRP", "ZCSAGFP", "PMPCMFP", "PMPCMF", "PMPCMP", "PBPPS", "PPMAGFP", "PPMAGF", "PPMAGP", "SDI-SDA", "SDI-SDAEX", "SDI-SDM", "PPMAGPGF", "QAISOP", "QAISO20KF", "QAISO20KP", "PBPMBFP", "PBPMBF", "PBPMBPP", "QASCATP", "QACBRM", "QACOBITF", "PPMPPCFP")

Why would you think Fluff's code would work with your array list within your macro when you specially said (in Message #1 ) that "I need the macro to keep all the Z codes and delete all the remainder, all the codes are in the A column." Also, your first message said you wanted to keep the codes beginning with Z but the red highlighted text above says the opposite. Also, I was not exactly sure what you are asking for in the blue highlighted text. Why don't you tell us exactly what you have, where you have it and what you want as a final result and where you want that final result to be located at.

 

[Fluff]

Adding to what Rick has said, could you also post your existing code?

 

[ceebeedee]

Hi

Sorry for the delay. The code below shows the codes I need to keep and column titles, along with my original request. The aim of the spreadsheet is to keep the codes listed plus any 'Z' codes however, the Z codes are added to by another department without notifying me, which is why I need the wildcard. Hope this helps explain.

Sub DeleteRows()

Dim List As Variant
Dim LR As Long
Dim r As Long
List = Array("QAPCCS", "QACAT", "PBPPPS", "AEPMTBAGPMF", "ZACOPPMAGFP", "QAFCCM", "QABRMP", "PPMBBCFP", "PPMBBCF", "PPMBBCP", "QAISOFOU", "QAPCCS", "QACGDPRP", "ZCSAGFP", "PMPCMFP", "PMPCMF", "PMPCMP", "PBPPS", "PPMAGFP", "PPMAGF", "PPMAGP", "SDI-SDA", "SDI-SDAEX", "SDI-SDM", "PPMAGPGF", "QAISOP", "QAISO20KF", "QAISO20KP", "PBPMBFP", "PBPMBF", "PBPMBPP", "QASCATP", "QACBRM", "QACOBITF", "PPMPPCFP")
LR = Range("B" & Rows.Count).End(xlUp).Row
For r = LR To 1 Step -1
If IsError(Application.Match(Range("B" & r).Value, List, False)) Then
Rows(r).Delete
End If
Next r

Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Centre"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Course Code"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Number"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Customer"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Start Date"
Range("G1").Select
ActiveCell.FormulaR1C1 = "End Date"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Trainer"
Range("A1").Select
Rows("1:1").Select
Selection.Font.Bold = True

End Sub


Many Thanks

Chris

 

[Fluff]

Try

If IsError(Application.Match(Range("B" & r).Value, List, False)) And Left(Range("B" & r), 1) <> "Z" Then

 

[ceebeedee]

Thanks

I will run this tomorrow at work and let you know how it goes. I'm assuming that I still need to keep the array list and place the code at the beginning of the routine?

Thanks, greatly appreciated.

Chris