Deleting Rows with certain text types

[Sharid]

Hi can someone help on a delete rows vba code

I need to delete all rows that contain the following, it checks column D for the data on Sheet

1. #VALUE!
2. More than 1 hyphen eg. Crocs-Womens-Shoes this no good, if there is 1 hyphen then leave that row
3. Numbers, these will be 3 or more numbers in a string /14768/ this is no good. However Crocs Shoes14768 or something like Crocs-Shoes147 is fine

 

[Sharid]

This is what I have so far, not the best

Private Sub CommandButton2_Click()

'RemoveDuplicates Columns D and sort out rows
    Dim WB As Workbook
    Dim total As Worksheet
    Dim cdLR As Long

    Set WB = Workbooks("URL AND TRIM MAKER.xlsm")
        Set total = WB.Sheets("Sheet2")
    
    With total
        cdLR = total.Cells(Rows.Count, "D").End(xlUp).Row
            Range("D" & cdLR).RemoveDuplicates Columns:=4
    End With

'RemoveKeyword types Columns D and sort out rows
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Dim value As String

    Set ws = ActiveWorkbook.Sheets("Sheet2")
        lastRow = ws.Range("D" & ws.Rows.Count).End(xlUp).Row

    ' Evaluate each row for deletion.
    For i = lastRow To 2 Step -1
        value = ws.Cells(i, 4).value ' Column D value.

    ' Check if it contains one of the keywords. THIS BIT IS NOT WORKING ALSO DONT KNOW HOW TO WRITE OTEHR KEYWORDS
    If InStr(value, "#VALUE!") <> 0 _
        Or InStr(value, "Can't work this out") <> 0 _
        Or InStr(value, "Can't work this out ") <> 0 _
    Then

        ws.Rows(i).Delete
    End If
    Next

End Sub

 

[Norie]

Is #VALUE! a string or an actual error?

 

[Sharid]

It as error, if that helps.

Also I tried to write the code as, if there are no duplicates then do nothing, but could not get it to work. As if the command button is clicked and column D is empty or has no duplicates I got an error message. If this this could be sorted and the other could not that would be still fine as the #VALUE! would be deleted as they are duplicated and only 1 will remain

 

[Norie]

The first thing you should do is check if there is an error value using IsError.

If there is delete the row, if there isn't then move onto checking for the other criteria.

So something like this.

For  i = lastRow To 2 Step -1

    If IsError(ws.Cells(i, 4).Value Then
        ws.Rows(i).Delete
    Else
        value = ws.Cells(i, 4).value ' Column D value. 

       ' check no of hyphens, if > 1 then delete row
        If Len(value) - Len(Replace(value, "-", ""))>1 Then
            ws.Rows(i).Delete
        End If

        ' do other checks

    End If

Next i

I've only posted the code for the 2nd criteria because I'm not 100% sure exactly what the 3rd is.

 

[Sharid]

I have two problems on this row

    If IsError(ws.Cells(i, 4).value Then

First Problem

If I wrap it in an extra bracket )

    If IsError(ws.Cells(i, 4)).value Then

Then the error goes, but when I run the code I get this
Second Problem

 

[Norie]

Sorry, wrote that code on the fly - try this.

For i = lastRow To 2 Step -1

    If IsError(ws.Cells(i, 4)) Then
        ws.Rows(i).Delete
    Else
        Value = ws.Cells(i, 4).Value ' Column D value.

       ' check no of hyphens, if > 1 then delete row
        If Len(Value) - Len(Replace(Value, "-", "")) > 1 Then
            ws.Rows(i).Delete
        End If

        ' do other checks

    End If

Next i

 

[Sharid]

SUPER