Macro to delete rows if they do not contain specific text

LSusiee · Sep 25th 2019

Hello,

I am currently working on a macro that will scan columns "E" and "I" and if they do not contain "Inserted" or "ClassID" the row should be deleted. I've tried multiple different variations but can't seem to get it right.. below is what I have so far:

Sub EvaluateD()
Dim i, LastRow
Dim UserIDModLog

UserIDModLog = ActiveSheet.Name

LastRow = UserIDModLog(Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If UserIDModLog.Value <> "Inserted" Or "ClassID" Then
Else
UserIDModLog.EntireRow.Delete
End If
Next i
End Sub

Help

Mumps · Sep 25th 2019

We need some clarification. Do you want to look for "Inserted" in column E and look for "ClassID" in column I or can the words be in either column? Please clarify in detail. If you could attach a copy of your file (de-sensitized if necessary), it would be easier to test a possible solution.

LSusiee · Sep 25th 2019

Hi!
Yes, I just need the rows that say Inserted or ClassID, everything else can be deleted. I have attached the document without the personal information so some columns will be empty. I've also attached a screenshot of what the end result should look like.

Thanks so much for your help!

sobi_256 · Sep 25th 2019

i have just started to learn but here what i wrote, there were extra spaces at the end of CLASSID so i had to use trim command, this works just tested it but the gurus of this forum might have a shorter and more precise way to do it.

Code

Sub rmtext()
Dim i As Long
Dim ii As Long
Dim j As Long


Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


ii = Cells(Rows.Count, 9).End(xlUp).row
    
For i = 1 To ii
        Cells(i, "I").Value = Trim(Cells(i, "I").Value)
Next i
i = 0
ii = Cells(Rows.Count, 5).End(xlUp).row
    
For i = 1 To ii
        Cells(i, "E").Value = Trim(Cells(i, "E").Value)
Next i
i = 0
For i = Cells(Rows.Count, 5).End(xlUp).row To 1 Step -1
If Cells(i, 5) = "Inserted" Or Cells(i, 5) = "ClassID" Then
Cells(i, 5).EntireRow.Delete
End If
Next i
For j = Cells(Rows.Count, 9).End(xlUp).row To 1 Step -1
If Cells(j, 9) = "Inserted" Or Cells(j, 9) = "ClassID" Then
Cells(j, 9).EntireRow.Delete
End If
Next j
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

Display More

LSusiee · Sep 25th 2019

Hi!
It looks like it deleted rows that contained Inserted or ClassID and those are the ones I need

sobi_256 · Sep 26th 2019

sorry my bad, check this out if i understand correctly

Code

Sub rmtext()
Dim i As Long
Dim irow As Long
Dim erow As Long
Dim rng As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


erow = Cells(Rows.Count, 5).End(xlUp).row ' count e rows
irow = Cells(Rows.Count, 9).End(xlUp).row
    
For i = 1 To irow
        Cells(i, "I").Value = Trim(Cells(i, "I").Value)
Next i
i = 0


    
For i = 1 To erow
        Cells(i, "E").Value = Trim(Cells(i, "E").Value)
Next i
i = 0


Set rng = Range("E1:I" & irow)


    With rng
        .AutoFilter Field:=1, Criteria1:="<>*Inserted*", Operator:=xlAnd, Criteria2:="<>*ClassID*"
        .AutoFilter Field:=5, Criteria1:="<>*ClassID*", Operator:=xlAnd, Criteria2:="<>*ClassID*"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
  AutoFilterMode = False


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Display More

[ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1223643}[/ATTACH]

Mumps · Sep 26th 2019

Try:

Code

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A1:K" & LastRow).AutoFilter Field:=5, Criteria1:="<>Inserted"
    Range("A1:K" & LastRow).AutoFilter Field:=9, Criteria1:="<>ClassID"
    Range("A2:K" & LastRow).EntireRow.SpecialCells(xlCellTypeVisible).Delete
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Macro to delete rows if they do not contain specific text

Participate now!

Share