Delete Rows by given criteria

[jawaharprm]

Hi All,
i need to delete rows not match with this criteria
in E Column keep only contains "E3(, E4( & Rx(" other than like E1( ect or L"I1(1),LIVE(2),RX(1)" ect, should be delete i wrote the code but its delete all the rows expect header pls help same, enclosed sample data for reference

my code

Option Explicit

Sub E3_E4_RX()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim val As String

Set ws = ThisWorkbook.Sheets("Technical sheet")
lastrow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

For i = lastrow To 2 Step -1
val = LCase(Trim(ws.Cells(i, 5).Value))

If Not (InStr(val, "E3(" ) > 0 Or InStr(val, "E4(" ) > 0 Or InStr(val, "RX(" ) > 0) Then

ws.Rows(i).Delete
End If
Next i
End Sub

 

[MikeVol]

Hi! Try next code:

Option Explicit

Sub E3_E4_RX_v2()
    Dim i           As Long
    Dim val         As String
    Application.ScreenUpdating = False

    With ThisWorkbook.Sheets("Technical sheet")
        
        Dim lastrow As Long
        lastrow = .Cells(.Rows.Count, "E").End(xlUp).Row

        For i = lastrow To 2 Step -1
            val = LCase(Trim(.Cells(i, 5).Value))

            If Not (val Like "e3(*" And InStr(val, ",") = 0 Or _
                    val Like "e4(*" And InStr(val, ",") = 0 Or _
                    val Like "rx(*" And InStr(val, ",") = 0) Then
                .Rows(i).Delete
            End If
        
        Next i
    
    End With

    Application.ScreenUpdating = True
End Sub

 

[jawaharprm]

Hi Thanks for your time,
its works perfect but there is data like eg- "E4(24),F8(2)" "E3(24),F8(2)" its is deleted, but these text should be not delete

 

[MikeVol]

Maybe like:

Option Explicit

Sub E3_E4_RX_v3()
    Dim i           As Long
    Dim val         As String
    Application.ScreenUpdating = False

    With ThisWorkbook.Sheets("Technical sheet")
        
        Dim lastrow As Long
        lastrow = .Cells(.Rows.Count, "E").End(xlUp).Row

        For i = lastrow To 2 Step -1
            val = LCase(Trim(.Cells(i, 5).Value))

            If Not ( _
                    val Like "e3(*" And (InStr(val, ",") = 0 Or val Like "e3(*,f8(*") Or _
                    val Like "e4(*" And (InStr(val, ",") = 0 Or val Like "e4(*,f8(*") Or _
                    val Like "rx(*" And InStr(val, ",") = 0) Then

                .Rows(i).Delete
            End If
        
        Next i
    
    End With

    Application.ScreenUpdating = True
End Sub

 

[jawaharprm]

Hi Thanks,
its work fine really super, if i get expect f8 like live or w or z i need add

val Like "e3(*" And (InStr(val, ",") = 0 Or val Like "e3(*,f8,live,w1(*") Or _
or need to copy and if not again

 

[vletm]

About Your in E Column keep only contains "E3(, E4( & Rx("
Please verify:
# Are Rx( and RX( different for You? ... there are none Rx(
# If one cell contains eg "E3(" and "X4(" then do it will stay?
Could You give expected verified result?
... including Your #5 reply

Please, focus only - what should stay?

Check my sample - press [ Do It ]-button

> Sheet1 (2) is Your original sheet

jawaharprm ... seems that You skipped to answer to my questions.

 

[jawaharprm]

Hi Sir,
Thanks for your time, in this code Text contains E1 are not removed and i worked on this with additional its works as per my requirement.

Option Explicit

Sub E3_E4_RX()
Dim i As Long
Dim val As String
Application.ScreenUpdating = False

With ThisWorkbook.Sheets("Technical sheet")
    
Dim lastrow As Long
lastrow = .Cells(.Rows.Count, "E").End(xlUp).Row

For i = lastrow To 2 Step -1
val = LCase(Trim(.Cells(i, 5).Value))
            
' Only keep rows that match these conditions
If Not ( _
        val Like "e3(*" Or _
        val Like "e3()*" Or _
        val Like "e3()*,f8(*" Or _
        val Like "e4(*" Or _
        val Like "e4()*" Or _
        val Like "e4()*,f8(*" Or _
        val Like "rx(*" Or _
        val Like "rx()*" _
        ) Then
.Rows(i).Delete
End If

Next i

End With

Application.ScreenUpdating = True

End Sub