VBA - Delete entire row if column contains certain text from a specific range of data

[spencerp237]

Dim lastRow As Integer


lastRow = Sheets("CustomReport").Cells(Rows.count, 1).End(xlUp).Row


For i = 1 To lastRow


If WorksheetFunction.IsErr(WorksheetFunction.Find(Sheets("CustomReport").Cells(i, 15), Sheets("Sheet8").Range("A2:A" & Sheets("Sheet8").Cells(Rows.count, 1).End(xlUp).Row))) = False Then
    Sheets("CustomReport").Cells(i, 15).EntireRow.Delete
End If


Next i

I am trying to have this macro loop through a data export. It looks in column "O" (15) on the "CustomReport" sheet and checks if it is in the specified range on "Sheet8".

When I run this, I get a "Run-time error'13': Type mismatch" error message.

 

[Takae]

The find formula of worksheet searches a specified letter in a cell.
The find statement searches a spesified letter/number in a range.
You want to know if the text is in a range or not. You can use countif formula, because find statement troublesome if it does not find it.
And when deleting a row, it is usual rule to loop from the bottom.
Hope this helps.

Sub test1()
Dim i, ws As Worksheet
Dim lastRow As Integer
Dim rng As Range
Set ws = Sheets("Sheet8")
Set rng = ws.Range(ws.Range("A2"), ws.cells(Rows.count, 1).End(xlUp))
With Sheets("CustomReport")
    lastRow = .cells(Rows.count, [COLOR=#ff0000][B]15[/B][/COLOR]).End(xlUp).Row
    For i = lastRow To 1 Step -1
        If WorksheetFunction.CountIf(rng, .cells(i, 15).Value) > 0 Then
            .Rows(i).Delete
        End If
    Next
End With
End Sub

 

[Trebor76]

Hi spencerp237,

Try this (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro2()

    Const lngStartRow As Long = 2 'Starting data row number. Change to suit.
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With Sheets("CustomReport")
        lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
        lngMyRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With
    
    With Sheets("CustomReport").Columns(lngMyCol)
        With Range(Cells(lngStartRow, lngMyCol), Cells(lngMyRow, lngMyCol))
            .Formula = "=VLOOKUP(CustomReport!O" & lngStartRow & ",Sheet8!A:A,1,FALSE)"
            ActiveSheet.Calculate
            .Value = .Value
        End With
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

    MsgBox "All rows from Col. O on the ""CustomReport"" tab that were not in the Col. A of ""Sheet8"" have now been deleted.", vbInformation

End Sub

Note as it doesn't loop it's very fast.

Regards,

Robert

 

[spencerp237]

Thanks Robert,

Just tried it out and it runs very quickly. However it is deleting a lot more rows than I want it too and I'm not sure why.

Do I need to change the * in the .Find("*") to something?

 

[spencerp237]

Takae,

Thank you as well. Yours works great! Knowing that I should loop from the bottom and better understanding of the find formula will definitely help me with other things as well!