delete rows not containing specific start with multiple criteria

pook_666

New Member
Joined
Aug 16, 2018
Messages
37
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!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
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.

Code:
For i=2 To LRow
'code
Next i

And when you delete a row:

Code:
i = i - 1
LRow = LRow -1
 
Last edited:

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
Hello Pook,

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

Code:
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

New Member
Joined
Aug 16, 2018
Messages
37
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
You're welcome Pook. I'm glad that we were able to help.
Thanks for the feed-back.

Cheerio,
vcoolio.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,966
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top