Cannot use named range when it is empty

Question

I have a named range lstVendors that refers to: =OFFSET(Data!$W$2,0,0,COUNTA(Data!$W$2:$W$400),1). I want this range to be populated when the workbook opens. I have the following code for this:

Private Sub Workbook_Open()
    Application.WindowState = xlMaximized
Dim rslt()
Dim i As Integer
Dim n As Integer
Dim startRng As Range
Dim DropDown1 As DropDown

ThisWorkbook.Sheets("Dashboard").Shapes("TextBox 6").Visible = False
'    Range("lstVendors").Offset(0, 0).Value = "Please Select..."
'    Set DropDown1 = ThisWorkbook.Sheets("Dashboard").DropDowns("Drop Down 1")
'    DropDown1.Value = 1
On Error Resume Next
If Not IsError(Range("lstVendors")) Then
    Range("lstVendors").ClearContents
End If
On Error GoTo 0
rslt = Application.Run("SQLite_Query", "path/to/my/sqlite", "SELECT PROGRAM_ID FROM VENDOR;")
Set startRng = Range("lstVendors")
i = 0
For n = 2 To UBound(rslt)
    Range("lstVendors").Offset(i, 0).Value = rslt(n)(0)
    i = i + 1
Next n
End Sub

It errors on the Set startRng = Range("lstVendors"). I know this is because there's nothing in the range when I'm trying to set it, because if I put one entry into the named range, the set works, however, I need it populated by the sqlite query on each open as the data changes.

Any suggestions much appreciated.

Answer 1

Try this. You have a dynamic range that doesn't evaluate after you clear the contents. To avoid this, there are probably several ways, but easy to simply hardcode the startRange variable so that it always points to Data!$W$2 address, which is (or rather, will become) the first cell in your lstVendors range.

Private Sub Workbook_Open()

Dim rslt()
Dim i As Integer
Dim n As Integer
Dim startRng As Range
Dim DropDown1 As DropDown
Dim rngList As Range

'// Define your startRange -- always will be the first cell in your named range "lstVendors"
'   hardcode the address because the dynamic range may not evalaute.
Set startRange = Sheets("Data").Range("W2")

'// Empty th lstVendors range if it exists/filled
On Error Resume Next
    Range("lstVendors").Clear
On Error GoTo 0

'// Run your SQL query
rslt = Application.Run("SQLite_Query", "path/to/my/sqlite", "SELECT PROGRAM_ID FROM VENDOR;")
i = 0

'// Print results to the Worksheet, beginning in the startRange cell
For n = 2 To UBound(rslt)
    'Increment from the startRange cell
    startRange.Offset(i, 0).Value = rslt(n)(0)
    i = i + 1
    'Verify that "lstVendors" is being populated
    Debug.Print Range("lstVendors").Address
Next n


End Sub

Answer 2

Thanks for the suggestions. Here is what I ended up doing in order to get around my problem. It involves adding something I didn't specify would be ok in my original question, so David's answer is great if what I did isn't an option. I first populated the first two cells in my named range with "Please Select..." and "All". In Sub Workbook_Open() we do this:

Private Sub Workbook_Open()
Application.WindowState = xlMaximized
Dim rslt()
Dim i As Integer
Dim n As Integer
Dim startRng As Range
Dim DropDown1 As DropDown

' Disable our not found message
ThisWorkbook.Sheets("Dashboard").Shapes("TextBox 6").Visible = False
' Set our start range to our named range
Set startRng = Range("lstVendors")
' Grab all vendor names
rslt = Application.Run("SQLite_Query", "path/to/my/sqlite", "SELECT PROGRAM_ID FROM VENDOR;")
' Print result. Skip first two rows as constants "Please Select..." and "All" are populated there
i = 2
For n = 2 To UBound(rslt)
    startRng.Offset(i, 0).Value = rslt(n)(0)
    i = i + 1
Next n
End Sub

Then we will create Sub Workbook_BeforeClose:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Disable the save changes dialog. This workbook will be locked up for display only. No need to confuse the user.
Application.DisplayAlerts = False
' Clear everything below the "Please Select..." and "All" cells in the named range
On Error Resume Next
Range("lstVendors").Offset(2, 0).ClearContents
On Error GoTo 0
' Save the changes to the named range
ThisWorkbook.Save
Application.DisplayAlerts = True
End Sub

This information is going to populate a drop down, so having Please Select and All hardcoded into the named range is acceptable for me. If that stipulation doesn't work for someone else looking at this in the future, please use David's suggestion! Thanks again!