Delete row above if partial match VBA

[pook_666]

Hi magicians,

Not sure if this can be done, but worth me asking!

I have the below table that can change in length (but never width). I want VBA to delete the row above if columns A, D, E + F match. (it's part of a wider VBA)

For example, the below I would want to delete row 8.

Is this at all possible? I was thinking of adding =A2&D2&E2&F2 in cell G2 then autofilter down then do a comparison to delete the row above if matches?

Any help greatly appreciated!

 

[mrshl9898]

Try:

Sub Remove()

Dim sht As Worksheet
Dim LastRow As Long
Dim RowNum As Long

Set sht = ActiveSheet

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

RowNum = 2

sht.Range("G2").FormulaR1C1 = _
        "=COUNTIFS(C[-6],RC[-6],C[-3],RC[-3],C[-2],RC[-2],C[-1],RC[-1])"
sht.Range("G2").Copy sht.Range("G2:G" & LastRow)

Do Until RowNum = LastRow
    If sht.Cells(RowNum, 7) > 1 Then
    sht.Rows(RowNum).EntireRow.Delete
    RowNum = RowNum - 1
    End If
RowNum = RowNum + 1
Loop


End Sub

 

[pook_666]

Ah so I was on the right lines....just didn't know how to execute it!

Thanks you @mrshl9898 this works perfectly!

 

[mrshl9898]

Happy to help