Index match to delete rows not in table

[dshafique]

Hi guys, Im trying to make a macros in which one of the things is to remove a row if a cell value isnt in another table.

for example, if an application under the column Unique ID does not exist in table "KPI" under the Unique ID column, then delete the whole row.

i was thinking of using index match, and came up with this

=INDEX(KPI, MATCH([@UniqueID],KPI[UniqueID],0))

it keeps returning a value of #REF !, and i know i did somehting wrong, but dont know what.

the current table is called aTable, the table i want to check against is called KPI, and both tables have the same names for the header columns.

any help would be greatly appreciated, thanks.

 

[igold]

If I understand your requirements, this code will remove any rows in "aTable" that does not have a match in the "UniqueID" column of the "KPI" table.

Sub tbls()


    Dim lat As Long, lkpi As Long
    Dim i As Integer, x As Long, kcol As Long, lcolat As Long, lcolkpi As Long
    Dim rng As Range
    Dim atbl As ListObject: Set atbl = ActiveSheet.ListObjects("aTable")
    Dim ktbl As ListObject: Set ktbl = ActiveSheet.ListObjects("KPI")


    lcolat = atbl.ListColumns("UniqueID").Index
    lcolkpi = ktbl.ListColumns("UniqueID").Index
    lkpi = ActiveSheet.ListObjects("KPI").ListColumns("UniqueID").Range.Cells.Count - 1
    lat = ActiveSheet.ListObjects("KPI").ListColumns("UniqueID").Range.Cells.Count - 1
    For i = 1 To lkpi
        For x = lat To 1 Step -1
            If ktbl.DataBodyRange(x, lcolkpi) = atbl.DataBodyRange(i, lcolat) Then
                GoTo fnd
            End If
        Next
        On Error GoTo errhandler
        atbl.ListRows(i).Delete
fnd:
    Next
errhandler:
End Sub