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

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?

Who is Participating?
Patrick MatthewsConnect With a Mentor 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

mlagrangeAuthor Commented:
Thanks, Patrick!
Patrick MatthewsCommented:
You're most welcome :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.