delete rows that do not contain a defined set

[nicksinthemi]

I have this clever piece of code that will delete a row if it contains a certain word. Anyone know how I:

1. restrict it to col A and,

2. delete the rows where the first 3 letters of the cell don't match a defined set

Sub test()
With ActiveSheet
.AutoFilterMode = False
With Range("d1", Range("d" & Rows.Count).End(xlUp))
.AutoFilter 1, "*Record Only*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

[mancubus]

Sub test()
With ActiveSheet
.AutoFilterMode = False
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.AutoFilter 1, "=xyz*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

[Bob Phillips]

Shouldn't that be

"<>xyz*"

[nicksinthemi]

ah yes, <>

but can i use multiple values with this, as in delete everything thats NOT "xyz" or "abc"

??

[nicksinthemi]

autofilter only works with 2 criteria - i suspect there is a way to do with wit advancedfilter

[mancubus]

Shouldn't that be

"<>xyz*"

sure.
:yes

sorry for misreading...

[mancubus]

http://www.xtremevbtalk.com/showthread.php?t=300757

deleting rows, different scenarios.

post #7 may help.

[nicksinthemi]

Turns out this is a lot more complicated but thanks for that link. I have a snip that will work if I can do this in two steps:

1. I'll try and blank out every cell that doesn't fit my 46 conditions (they all take the form of "ABC*")

2. I'll run this concat code

Application.ScreenUpdating = False

With ActiveSheet

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = lastrow To 2 Step -1

If .Cells(i, "A").Value = "d" Then

.Cells(i - 1, "B").Value = .Cells(i - 1, "B").Value & " " & .Cells(i, "B").Value
.Rows(i).Delete
End If
Next
End With

Application.ScreenUpdating = True

I know the second one will loop through all my files in a directory successful. This is my last step to normalisation.

Any idea on what a 'Clear the cell in row A if it doesn't start with "ABC*", "BCD*"....) would look like?

Thanks for all your help. So far I have learned quite a bit about VBA. My reformatting script is really starting to grow but with this last step I think it will finally finish.

[Bob Phillips]

If .Evaluate("SUMPRODUCT(COUNTIF(A" & i & ",{""ABC*"",""DEF*"",""XYZ*""}))") = 0 Then

.Cells(i, "A").ClearContents
End If