Deletring Rows That Don't Match Criteria

[glerwell]

Hi

I looking at some past data from last years Premiership. What I want to do is streamline the data that I have, so what I would like to do is delete the rows that don't match a criteria.

The criteria must be in either column B or C otherwise that row is deleted.

eg. the Criteria is "CHELSEA" so any rows that don't contain "CHELSEA" in columns B or C are deleted.

I know it would probably have to be a VBA code to do but I'm don't where to start.

Any help woulb be greatly appreciated!

Cheers

 

[kpark91]

My bad for the late reply

Sub DeleteRows()
Dim lr As Integer
 
Application.ScreenUpdating = False
 
lr = Range("A" & Rows.Count).End(xlUp).Row
 
For c = lr To 1 Step -1
If InStr(1, Range("B" & c).Value, Range("A3").Value, 0) = 0 Then
If InStr(1, Range("C" & c).Value, Range("A3").Value, 0) = 0 Then
Rows(c).Delete
End If
End If
Next c
 
Application.ScreenUpdating = True
End Sub

 

[glerwell]

thanks