Hi

Try:

'==================
Public Sub DeleteRows()
Dim rng As Range
Dim rCell As Range
Dim delRng As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim Arr As Variant
Dim CalcMode As Long

Set WB = ActiveWorkbook '<<======== CHANGE
Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
Set rng = SH.Range("A1:A100") '<<======== CHANGE

Arr = Array("BOS", "CARDSAVE", "HSBC") '<<===== CHANGE

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

For Each rCell In rng.Cells
If Not IsError(Application.Match(rCell.Value, Arr, 0)) Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
Else
'nothing found, do nothing
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'==================

---
Regards,
Norman



"5tin@" wrote in message
...

Hi all,

Is it possible to use VB to delete rows that do not contain a certain
value. For example if the value in column I did not contain "BOS"
"CARDSAVE" "HSBC" those rows would be deleted?

Any help would be amazing!!

Thanks


--
5tin@
------------------------------------------------------------------------
5tin@'s Profile:
http://www.excelforum.com/member.php...o&userid=27499
View this thread: http://www.excelforum.com/showthread...hreadid=470138