Compare date in a cell to a variable, then delete row.

[dhosi439]

Is it possible to compare data in a range of cells to a concatenated variable in vba, if it is found then delete the entire row, otherwise do nothing.

For example,

range is A:A, value to be located is B27_3/17/2010

variables are test1 and test2

test1 = B27 test2 = 3/17/2010

the concatenated would look like this: test1 & "_" & test2 or 'B27_3/17/2010'

I need to look in column A for the concatenated variables, if it is found then the row containing that value must be deleted.

Not sure how to search for the value in the range.

The values are in a table and column A is the first column of the table and this can be a named range, if this makes it easier.

 

[Domenic]

Does Row 1 contain column labels/headers? If not, include the labels/headers, and then try...

Sub test()

    Dim FoundCells As Range
    Dim test1 As String
    Dim test2 As String
    
    test1 = "B27"
    test2 = "3/17/2010"
    
    Application.ScreenUpdating = False
    
    With ActiveSheet.UsedRange
        .AutoFilter field:=1, Criteria1:=test1 & "_" & test2
        On Error Resume Next
        Set FoundCells = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not FoundCells Is Nothing Then
            FoundCells.EntireRow.Delete
        Else
            MsgBox "No cells were found..."
        End If
        .AutoFilter
    End With
    
    Application.ScreenUpdating = True

End Sub