Excel VBA - deleting rows which do not contain certain text

[cjh1984]

Hi,

I need to be able to automatically delete rows of data within
spreadsheet where the data in Column B does not contain, for exampl
SEOP. But this data is stored as SEOP Germany. So it needs to look fo
those cells which do not have it somewhere within that text.

Is this possible and if so how

 

[DNF Karran]

Yes-

Option Explicit
Sub DeleteRows()
Dim rngCur As Range, a As String
On Error GoTo ErrHandle

For Each rngCur In Range("B1:B"
Range("B1").SpecialCells(xlCellTypeLastCell).Row)
If WorksheetFunction.Find("SEOP", rngCur) <> Empty Then
rngCur.EntireRow.Clear
End If
ResumeLine:
Next
Exit Sub
ErrHandle:
If Err.Number = 1004 Then Resume ResumeLine
End Sub


Note- I have cleared the cells to avoid having to "step back" up by
row when the delete occurs. These blanks could be removed from the dat
using a sort or changing "clear" to "delete" and adding rngCur
rngcur.Offset(-1,0) on the line below. The function FIND() is also cas
sensitive.

Dunca

 

[Frank Kabel]

Hi
try the following macro:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If InStr(Cells(row_index, "B").Value,"SEOP")=0 then
Cells(row_index, "B").EntireRow.delete
End If
Next
Application.ScreenUpdating = True
End Sub

 

[DaveR_PA]

Hello,

Alternatively, how does one delete the rows that do contain certain text? Thanks so much!