Excel VBA - delete cells containing not equal

[cjh1984]

Hi all,

I am using the code below to delete rows which contain, in thi
example, SEOP. Where it says Value = "SEOP", what can I use so i
deletes values NOT CONTAINING SEOP?


Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "B").Value) Then
l

ElseIf .Cells(Lrow, "B").Value = "SEOP" The
.Rows(Lrow).Delete


End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End Wit

 

[Guest]

Use <> instead of =

 

[cjh1984]

Yes I have used this but it does not do everything I require, I need t
keep the cells which have SEOP within them. E.g. SEOP Germany. So i
needs to delete rows where the cell in column B does not contain SEO
anywhere within that text.

Does that make sense and is this possible to do!

Thank

 

[Ron de Bruin]

Hi

Try this one


Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If .Cells(Lrow, "B").Value Like "*SEOP*" Then
' do nothing
Else
.Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub