VBA Delete Area if first to characters start with "LS" or "BL"

[Stephen_IV]

I have course numbers in Column E. If the first to characters start with "LS" or "BL" then I would like the entire student deleted. I highlighted it in red so you can see the ones I would like deleted.

Book10
	A	B	C	D	E	F
1	ST #	LASTFIRST	TERMID	SCHOOLID	COURSE_NUMBER	COURSE_NAME
2
3	1111	xxxx xxxxxxxx	2702	111111	MA301YMAMA	PROBABILITY & STATISTICS - MAGNET
4	1111	xxxx xxxxxxxx	2700	111111	AD011YPLGE	HOMEROOM GRADE 11
5	1111	xxxx xxxxxxxx	2702	111111	LS102YTDAP	AP CAPSTONE 1-AP SEMINAR
6	1111	xxxx xxxxxxxx	2702	111111	PE100YTDGE	FULL YEAR PHYSICAL EDUCATION
7	1111	xxxx xxxxxxxx	2702	111111	SS301YMAMA	US HISTORY - MAGNET
8
9	2222	xxxx xxxxxxxx	2702	222222	SC110YMAMA	BIOLOGY - MAGNET
10	2222	xxxx xxxxxxxx	2702	222222	EN101YMAMA	ENGLISH 9 - MAGNET
11	2222	xxxx xxxxxxxx	2702	222222	MA101YMAMA	ALGEBRA I - MAGNET
12	2222	xxxx xxxxxxxx	2702	222222	CS111YMAMA	INTRODUCTION TO JAVA
13	2222	xxxx xxxxxxxx	2700	222222	AD009YPLGE	HOMEROOM GRADE 9
14
15	3333	xxxx xxxxxxxx	2700	111111	AD009YPLGE	HOMEROOM GRADE 9
16	3333	xxxx xxxxxxxx	2702	111111	BL101YMAMA	ENGLISH 9 - MAGNET
17	3333	xxxx xxxxxxxx	2702	111111	MA101YMAMA	ALGEBRA I - MAGNET
18	3333	xxxx xxxxxxxx	2702	111111	CS111YMAMA	INTRODUCTION TO JAVA
19	3333	xxxx xxxxxxxx	2702	111111	WL101YMAMA	SPANISH I - MAGNET
20
21	4444	xxxx xxxxxxxx	2702	333333	SC311YMAMA	HUMAN PHYSIOLOGY
22	4444	xxxx xxxxxxxx	2702	333333	MA202YMAMA	ALGEBRA II - MAGNET
23	4444	xxxx xxxxxxxx	2700	333333	AD010YPLGE	HOMEROOM GRADE 10
24	4444	xxxx xxxxxxxx	2706	333333	SS412SMAMA	EARLY AMERICAN REPUBLIC - MAGNET
25	4444	xxxx xxxxxxxx	2702	333333	SC420YMAMA	INTRODUCTION TO PHARMACOLOGY
26	4444	xxxx xxxxxxxx	2705	333333	SS201SMAMA	CIVICS - MAGNET
27
28	5555	xxxx xxxxxxxx	2700	222222	AD009YPLGE	HOMEROOM GRADE 9
29	5555	xxxx xxxxxxxx	2702	222222	WL101YMAMA	SPANISH I - MAGNET
30	5555	xxxx xxxxxxxx	2702	222222	MA101YMAMA	ALGEBRA I - MAGNET
31	5555	xxxx xxxxxxxx	2702	222222	SC110YMAMA	BIOLOGY - MAGNET
32	5555	xxxx xxxxxxxx	2705	222222	SS201SMAMA	CIVICS - MAGNET
33	5555	xxxx xxxxxxxx	2706	222222	SS412SMAMA	EARLY AMERICAN REPUBLIC - MAGNET
34
35	7777	xxxx xxxxxxxx	2702	333333	SC110YMAMA	BIOLOGY - MAGNET
36	7777	xxxx xxxxxxxx	2700	333333	LS012YPLGE	HOMEROOM GRADE 12
37	7777	xxxx xxxxxxxx	2702	333333	WL101YMAMA	SPANISH I - MAGNET
38	7777	xxxx xxxxxxxx	2706	333333	SS412SMAMA	EARLY AMERICAN REPUBLIC - MAGNET
39	7777	xxxx xxxxxxxx	2705	333333	SS201SMAMA	CIVICS - MAGNET
40	7777	xxxx xxxxxxxx	2700	333333	AD009YPLGE	HOMEROOM GRADE 9
41
42	8888	xxxx xxxxxxxx	2702	111111	AD014YPLGE	EARLY DISMISSAL
43	8888	xxxx xxxxxxxx	2700	111111	AD012YPLGE	HOMEROOM GRADE 12
44	8888	xxxx xxxxxxxx	2702	111111	CS137YMAMA	DIGITAL ACTIVISM/SOCIETY
45	8888	xxxx xxxxxxxx	2706	111111	HE101STDGE	HEALTH
46	8888	xxxx xxxxxxxx	2705	111111	MA507SMAMA	BUSINESS MATH
47	8888	xxxx xxxxxxxx	2702	111111	EN401YMAMA	ENGLISH 12 - MAGNET
48
Sheet1

 

[veyselemre]

Hi.

Sub test()
    Dim lst, i&, ky$, y, sil As Boolean, rng As Range, lr&
    lr = Cells(Rows.Count, 1).End(3).Row
    lst = Range("A1:F" & lr).Value

    Set rng = Range(lr + 2 & ":" & lr + 2)
    With CreateObject("Scripting.Dictionary")
        For i = 3 To UBound(lst)
            ky = lst(i, 1)
            sil = (Left(lst(i, 5), 2) = "LS" Or Left(lst(i, 5), 2) = "BL")
            If Not .exists(ky) Then
                .Item(ky) = Array(i, i, sil)
            Else
                y = .Item(ky)
                y(1) = i
                If y(2) <> True Then y(2) = sil
                .Item(ky) = y
            End If
        Next i
        For Each y In .items
            If y(2) = True Then
                Set rng = Union(rng, Range(y(0) & ":" & y(1) + 1))
            End If
        Next y
        rng.Delete
    End With
End Sub

 

[Stephen_IV]

Perfect!!!! Exactly what I needed!!! Thank you so much!