Code to keep certain rows

[Dazzawm]

Hi I need a code that will look in column J and keep any row that has (WSL) in the cell and delete all the others. (WSL) or (wsl) will be among other data in the cell, either at the beginning, middle or end so I cant do a sort. Thanks.

 

[Richard Schollar]

Hi

Dim r As Range
 
Set r = Intersect(Activesheet.UsedRange,Range("J:J"))
 
Activesheet.Autofiltermode = False
 
r.Autofilter Field:=1,Criteria1:="<>*WSL*"
 
r.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
 
Activesheet.AutofilterMode = False

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("J" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Not UCase(Range("J" & i).Value) Like "*WSL*" Then Rows(i).Delete
Next i
End Sub

 

[Dazzawm]

Thanks both, Richard yours worked and VoG yours deleted everything!

 

[VoG]

VoG yours deleted everything!

It didn't when I tested it but Richard's should be faster anyway.

 

[Dazzawm]

I believe there is a line or two of code that will display a msgbox at the end telling me how many rows have been deleted and how many have been kept. What are these please? Thanks.

 

[ponnars]

I don't know how to do it with Richard's code.

But i've modified the code by VoG slightly

Sub test()
Dim LR As Long, i As Long
Dim Del As Long
Dim kept As Long
Del = 0
kept = 0
LR = Range("J" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If UCase(Range("J" & i).Value) Like "*WSL*" Then
    Rows(i).Delete
    Del = Del + 1
    Else
    kept = kept + 1
    End If
Next i
MsgBox Del & " rows deleted and " & kept & " rows kept."
End Sub

Sorry if it doesn't help.

 

[VoG]

Try like this

Dim LR1 As Long, LR2 As Long
LR1 = Range("J" & Rows.Count).End(xlUp).Row
'
'code for deleting
'
LR2 = Range("J" & Rows.Count).End(xlUp).Row
MsgBox LR1 - LR2 & " rows deleted" & vbNewLine & LR2 & " rows retained", vbInformation

 

[Dazzawm]

Try like this

Dim LR1 As Long, LR2 As Long
LR1 = Range("J" & Rows.Count).End(xlUp).Row
'
'code for deleting
'
LR2 = Range("J" & Rows.Count).End(xlUp).Row
MsgBox LR1 - LR2 & " rows deleted" & vbNewLine & LR2 & " rows retained", vbInformation

Is this for use with Richards code?

 

[VoG]

Yes. Just put his code where I put "code for deleting".