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]

Put this in your worksheet VBE

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

 

[Jonmo1]

You're going to want to make that go backwards...

Change

For c = 1 To lr
to
For c = lr To 1 Step -1


And

Row(c).Delete
should be
Rows(c).Delete

 

[kpark91]

And also in that case, delete c = c- 1

 

[glerwell]

I just tried this code:

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, "CHELSEA") = 0 Then
If InStr(1, Range("C" & c).Value, "CHELSEA") = 0 Then
Rows(c).Delete
End If
End If
Next c

Application.ScreenUpdating = True
End Sub

And all of the data was deleted?

 

[kpark91]

No... It should be right ....

So, if CHELSEA is found in column B OR C, you want them to be kept right?

 

[glerwell]

yeah thats right

 

[kpark91]

Umm.... Does Capitalization matter?

 

[glerwell]

Yes thats what it was!!

Thanks

 

[glerwell]

Could this modified so that the criteria is whatever the value of A1 is. This way I could use it with other teams without the need to manually change the code.

So if A1 = "ARSENAL" then all the Arsenals would be left

This would need it to start at A3 though, so that the top two rows are not deleted as they would always be needed.

Thanks