Deleting rows based on slight changes to a text string

[Mpotten]

HI All

in need some help in refining some code. I am trying to delete rows based on a value of a cell in column A.

the problem i have is that sometime the value entered is slightly different so the code stops working. The problem i have is that sometimes the data does not have the brackets.

ESL Bioscience (NZ) Ltd
WPMP Services (Ray Percival)

become

ESL Bioscience NZ Ltd
WPMP Services Ray Percival

This is what i have so far

Sub Delete_ESL_WPMP()

'loop thriugh data to remove ESL Bioscience (NZ) Ltd and WPMP Services (Ray Percival)


    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = FinalRow To 2 Step -1
        If Cells(i, 1).Value = "WPMP Services (Ray Percival)" Then
        Cells(i, 1).EntireRow.Delete
            
            If Cells(i, 1).Value = "ESL*" Then
            Cells(i, 1).EntireRow.Delete
            End If


        End If
    Next i
    
End Sub

 

[JoeMo]

Try:

If Cells(i, 1).Value Like "WPMP Services *Ray Percival*" Then         
Cells(i, 1).EntireRow.Delete 
                         If Cells(i, 1).Value Like "ESL*" Then
            Cells(i, 1).EntireRow.Delete
            End If

 

[Rick Rothstein]

Try:

If Cells(i, 1).Value Like "WPMP Services *Ray Percival*" Then         
Cells(i, 1).EntireRow.Delete 
                         If Cells(i, 1).Value Like "ESL*" Then
            Cells(i, 1).EntireRow.Delete
            End If

We don't know the OP's data, so that first test could be too aggressive. For example, what if there was this in the cell...

WPMP Services for the Elderly (Ray Percival)

The OP is going to have to give us a little insight on what kind of values could be in the cells.

 

[My Aswer Is This]

Try this:

Sub Delete_ESL_WPMP()

'loop thriugh data to remove ESL Bioscience (NZ) Ltd and WPMP Services (Ray Percival)

    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = FinalRow To 2 Step -1
        
        lngPos = InStr(Cells(i, 1).Value, "Ray Percival")
        lngPos2 = InStr(Cells(i, 1).Value, "ESL")
        
If lngPos > 0 Or lngPos2 > 0 Then Cells(i, 1).EntireRow.Delete
          
 Next i
    
End Sub

 

[Rick Rothstein]

Try this:

Sub Delete_ESL_WPMP()

'loop thriugh data to remove ESL Bioscience (NZ) Ltd and WPMP Services (Ray Percival)

    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = FinalRow To 2 Step -1
        
        lngPos = [B][COLOR="#FF0000"]InStr(Cells(i, 1).Value, "Ray Percival[/COLOR]")[/B]
        lngPos2 = InStr(Cells(i, 1).Value, "ESL")
        
If lngPos > 0 Or lngPos2 > 0 Then Cells(i, 1).EntireRow.Delete
          
 Next i
    
End Sub

That too may be too aggressive... the OP did not say he wanted to delete everything with Ray Percival's name in it, only a certain text string with it.

 

[JoeMo]

We don't know the OP's data, so that first test could be too aggressive. For example, what if there was this in the cell...

WPMP Services for the Elderly (Ray Percival)

The OP is going to have to give us a little insight on what kind of values could be in the cells.

The OP stated- "The problem i have is that sometimes the data does not have the brackets." and that's what the code I posted intends to address. As you may have experienced from time to time, getting the kind of insight that allows any solution to be bulletproof is a rare event, if ever. So, in this case I went with the best information available.

 

[Rick Rothstein]

The OP stated- "The problem i have is that sometimes the data does not have the brackets." and that's what the code I posted intends to address. As you may have experienced from time to time, getting the kind of insight that allows any solution to be bulletproof is a rare event, if ever. So, in this case I went with the best information available.

Yes, I understand that... while I posted my message against your posting, it was really meant as an alert to the OP that it might delete more than the OP thinks it will be deleting.

 

[hiker95]

Mpotten,

If I understand you correctly, here is a macro solution for you to consider, based on the raw data that you have posted so far, that does not do any looping.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007
	A
1
2	ESL Bioscience (NZ) Ltd
3	Mpotten
4	WPMP Services (Ray Percival)
5	Mpotten
6	ESL Bioscience NZ Ltd
7	Mpotten
8	WPMP Services Ray Percival
9	Mpotten
10
Sheet1

After the macro:

Excel 2007
	A
1
2	Mpotten
3	Mpotten
4	Mpotten
5	Mpotten
6
7
8
9
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub Delete_ESL_WPMP_V2()
' hiker95, 06/01/2015, ME858352
With Sheets("Sheet1")   '<-- you can change the sheet name here
  With .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
  .Replace "WPMP Services (Ray Percival)", "#N/A", xlWhole, xlByRows, False
  .Replace "WPMP Services Ray Percival", "#N/A", xlWhole, xlByRows, False
  .Replace "ESL*", "#N/A", xlWhole, xlByRows, False
  .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete xlUp
  End With
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Delete_ESL_WPMP_V2 macro.