Deleting rows based on table headers

[Cyclops755]

Hey there everyone,
I'm trying to find some code that will compare the header column entries of table A, to the header row entries of table B; and delete any rows in A if an exact match isn't found in B.
Is anyone aware of how this can be done?
Thank you!

 

[Weaver]

Since we're talking about headers, do you mean columns (up & down) rather than rows (side to side)?

 

[Cyclops755]

Both. In table A, I'm looking to compare the values in the first column (header column), to the values in the first row of table b (header row).

 

[Weaver]

Are the tables on separate sheets? It's going to need code, so it might be a good idea for you to give the names of the worksheets involved and the ranges of where the data can be found.

 

[Cyclops755]

yes, the tables are on separate sheets, named "Combine Build List" and "Parts List. Sorry for the late reply, I've been out of the office. Appreciate the help

 

[Weaver]

Okay, try this. I still wasn't sure which data was where, so I used my own names. You ought to be able to figure it out

Sub deleteReferences()
    Dim hdr As Worksheet, dta As Worksheet
    Set hdr = Sheets("header list") 'where the vertical list of names is
    Set dta = Sheets("datatable") 'where the full dataset with headers is
    h = 5 'header row
    With hdr
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        For r = lr To 1 Step -1 'assumes header list starts in row 1
            v = .Cells(r, "A").Value
            If dta.Rows(h).Find(v) Is Nothing Then .Rows(r).EntireRow.Delete
        Next r
    End With
End Sub

 

[Cyclops755]

Thanks for the code. Input my sheet names and tested, but it doesn't delete any rows, and instead simply clears almost all the names in the header column, and I get a run-time error 1004: Application or User-Defined error.

Current Code:

Sub deleteReferences()
    Dim hdr As Worksheet, dta As Worksheet
    Set hdr = Sheets("Combine Build List") 'where the vertical list of names is
    Set dta = Sheets("Parts List") 'where the full dataset with headers is
    h = 5 'header row
    With hdr
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        For r = lr To 1 Step -1 'assumes header list starts in row 1
            v = .Cells(r, "A").Value
            If dta.Rows(h).Find(v) Is Nothing Then .Rows(r).EntireRow.Delete
        Next r
    End With
End Sub

One other note, is that I eventually will need to also be able to add rows in a similar fashion to deleting them. Though that shouldn't be a hard adaptation of the "delete" code once we can get it working.

 

[Weaver]

Are your sheets set up as suggested? i.e is your header row on row 5 in the 'parts list' sheet?