How to make data validation list drop-down appear in first column of new row in VBA?

mlagrange
mlagrange used Ask the Experts™
on
How do you set up the table and/or data validation list to be available on the first blank row of a table in VBA?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
An approach that worked for me:

1) Create your table

2) Resize your table so that it includes an empty row at the bottom

3) Add this code to the sheet module for the worksheet with the table, to force the table to resize with the extra blank row

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim lo As ListObject
    Dim loRng As Range
    Dim loLastRow As Range
    
    Set lo = Me.ListObjects(1)
    Set loRng = lo.Range
    Set loLastRow = loRng.Rows(loRng.Rows.Count)
    
    If Not Intersect(loLastRow, Target) Is Nothing Then
        Application.EnableEvents = False
        lo.Resize loRng.Cells(1).Resize(loRng.Rows.Count + 1, loRng.Columns.Count)
        Application.EnableEvents = True
    End If
    
End Sub

Open in new window

Author

Commented:
Thanks, Patrick!
Top Expert 2010

Commented:
You're most welcome :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial