VBA help-delete multiple string

[Isabella]

Hi, i am after a vba code that will delete, BULK, MISC, FORWARD and OFFICE

Excel Workbook
	C
1	CODE
2	BERZINS
3	MISC
4	MISC
5	BARNES
6	CASTLE
7	GESHOSG
8	ERDMAN
9	OFFICE
10	BATEMAN
11	BULK
12	BULK
13	MASTERS
14	FORWARD
15	ORLEYM
Sheet1

 

[GTO]

Maybe:

Option Explicit
    
Sub exa1()
Dim REX As Object
Dim lLRow As Long, i As Long
    lLRow = Cells(Rows.Count, "A").End(xlUp).Row
    If lLRow < 2 Then Exit Sub
    Set REX = CreateObject("VBScript.RegExp")
    With REX
        .Global = False
        .IgnoreCase = True '<---change to suit
        .Pattern = "^(MISC|OFFICE|BULK|FORWARD)$"
        For i = lLRow To 2 Step -1
            If .Test(Cells(i, "A").Value) Then Rows(i).Delete
        Next
    End With
End Sub

Hope that helps,

Mark

 

[Mike Blackman]

Hi,

Maybe:

Sub RemoveValues()

    Dim rngCell As Range
    Dim lngLastRow As Long, lngRow As Long
    
    lngLastRow = Range("C" & Rows.Count).End(xlUp).Row
    
    For lngRow = lngLastRow To 2 Step -1
        Select Case Range("C" & lngRow)
            Case "MISC", "OFFICE", "BULK", "FORWARD"
                Range("C" & lngRow).Delete Shift:=xlUp
        End Select
    Next lngRow

End Sub

 

[Isabella]

Mark, why use .Test?

Maybe:

Option Explicit
    
Sub exa1()
Dim REX As Object
Dim lLRow As Long, i As Long
    lLRow = Cells(Rows.Count, "A").End(xlUp).Row
    If lLRow < 2 Then Exit Sub
    Set REX = CreateObject("VBScript.RegExp")
    With REX
        .Global = False
        .IgnoreCase = True '<---change to suit
        .Pattern = "^(MISC|OFFICE|BULK|FORWARD)$"
        For i = lLRow To 2 Step -1
            If .Test(Cells(i, "A").Value) Then Rows(i).Delete
        Next
    End With
End Sub

Hope that helps,

Mark

 

[GTO]

Hi Isabella,

.Test returns TRUE if we made a match; where the match could be any of the values in the pattern. The ^$ denote the start and end of the string, so we make sure we don't falsely match BULKHEAD or such.

That said, I would use Mike's.

Mark