Data validation - applying & listing via VBA

[Dr. Demento]

I have a table and I'd like to apply data validation to the entire table column based on the header (ID, BirthDate, Sex, Source, etc).

I have two questions:

1) I have code from another project that I use for any situation, but I'm curious if there's a more efficient coding that I could use specifically for a table
Universal code:

Case .value Like "*SEX*"
  For Each cell In Intersect(hdrCell.EntireColumn, rngUsed).Offset(1, 0).Resize(rngUsed.Rows.Count - 1)
    ' ~~ apply data validation ~~

2) after applying the data validation, I'd like each new row to "inherit" the column data validation (critical):
A) without having to re-run the macro and
B) confine data validation to the table column only (not have entire column with data validation).

Thoughts anyone?

Thanks y'all.

As an aside, is there any way to create a list of data validation ranges/criteria? Kind of like what you can do with F3 | Paste List for named ranges/tables or what Jeff Weir did here (Easy way to back up CF formats)??

 

[pbornemeier]

I when data validation is applied to a table subsequent rows added to that table will have inherit that validation, but if multiple rows are added (pasted) to the table at once, the data validation exists, but does not change any pasted data, it is applied only if changes are made to the pasted data.

Very crude code.  Review Validation Object in VBA help to expand

Sub DocumentCellValidation()

    Dim rngCell As Range
    Dim x
    
    On Error Resume Next
    For Each rngCell In ActiveSheet.UsedRange.Cells
        x = rngCell.Validation
        If Err.Number <> 0 Then Debug.Print rngCell.Address, rngCell.Validation.Type
        Err.Clear
    Next
    
End Sub

 

[Dr. Demento]

Thanks so much! That's too easy (when someone else provides the answer

However, with applying validation to new rows -- since each column has different validation (mostly from lists), would I use OFFSET to "copy" the validation from the cell above and apply it, column by column, to the new row??

 

[pbornemeier]

When you create a table and establish (possibly different) validation in each cell in the first row, each column's unique validation is carried down that column as the table is expanded. Tested with Excel 2010.

 

[Dr. Demento]

So by adding a row to the bottom of the table and then pasting the data in the new, empty row, it will automatically inherit the validation for each column? Cool!

Thanks so much.