Excel VBA - deleting rows which do not contain certain text

  • Thread starter Thread starter cjh1984
  • Start date Start date
C

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
 
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
 
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
 
Hello,

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