delete rows not containing specific start with multiple criteria

[pook_666]

Hi experts!

I have a problem with trying to write a VBA code...have tried creating & looked around the web and have so far failed miserably with this.

I am trying delete rows NOT starting with three letters - specifically HKG, BJS and SYD.

The table looks like the below with the data being in column A and starting in cell A2 (end is changeable as this data gets updated).

The start will always begin with these above three characters and I am only wanting rows with these beginnings in my table i.e. not having LAXEXJPL and LONEXJTY as per example below. The letters after BJS, HKG & SYD will change, hence needing a code for just beginning with these letters.

I have tried various things (annoying that autofilter "begins with" can only use two criteria!) but can't seem to do this......can anyone help please?

(FYI this is part of a bigger macro so needing VBA to make my dreams come true)

Name
BJSCGOOL
LAXEXJPL
HKGCGOPT
SYDCOMLV
LONEXJTY

<tbody>
</tbody>

Thanks in advance!

 

[Tim_Excel_]

This is easily doable.

1. Loop through all cells in column A, until the last row (guide to determine last row)
2. Use a LEFT() function to strip the first 3 chars from the cell's value (MDOCS page)
3. Use an IF NOT statement in conjunction with an OR statement to check whether the cell of the current iteration does not start with one of those letter codes you mentioned
4. Delete the row if the statement is true

I advice you to use a For ... To loop, as a For ... Each loop will skip a row in the loop if you delete one.
e.g.

For i=2 To LRow
'code
Next i

And when you delete a row:

i = i - 1
LRow = LRow -1

 

[vcoolio]

Hello Pook,

You can still use autofilter with a little work around as follows:-

Sub Test()

        Dim lr As Long, ar As Variant, c As Range
        lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
        ar = Array("BJS", "HKG", "SYD")
      
Application.ScreenUpdating = False

For i = 0 To UBound(ar)
        For Each c In Sheet1.Range("A2:A" & lr)
        If Left(c.Value, 3) = ar(i) Then
        c.Offset(, 7) = "X"
        End If
    Next c
Next i
With Sheet1.[A1].CurrentRegion
        .AutoFilter 8, "<>" & "X"
        .Offset(1).EntireRow.Delete
        .AutoFilter
End With

Sheet1.Columns(8).Clear
Application.ScreenUpdating = True

End Sub

It uses Column H as a helper column. If the three letters in the array(ar) match your required three letters in any cell in Column A, then "X" is placed in the relevant cell in Column H which basically marks the relevant row as one NOT to be deleted. The code then filters Column H for cells that are not equal to "X" and deletes the relevant rows.

I don't know how many columns you have so you may have to change the helper column to suit. I have also used sheet(Sheet1) codes in the macro above not the sheet name.

Please test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

 

[pook_666]

Thank you both for your help! I used a bit of both of your comments in the end to help me do this

Think I was making it more difficult than it should be...this is why you guys are the real superheros of this world!

 

[vcoolio]

You're welcome Pook. I'm glad that we were able to help.
Thanks for the feed-back.

Cheerio,
vcoolio.