excelvbadynamiclistboxuserform

Dynamic rowsource for listbox in excel user form

I have a question regarding listbox control in userform in ms excel.

How can I limit the search to the most recent 10 records in listbox instead of all of them?

How can this code can work for listbox rowsource?

.lstDatabase.RowSource = "Database! A & iRow-10 & :I" & iRow
Solution

  • Try this code, please. It assumes that "lstDatabase" is your list box name:

    Sub LoadLast10Rows()
 Dim sh As Worksheet, arr10 As Variant, lastRow As Long
 Set sh = ActiveSheet 'use here the sheet you need. Maybe Sheets("Database")
 lastRow = sh.Range("A" & Rows.Count).End(xlUp).row
 arr10 = sh.Range("A" & lastRow - 10 & ":A" & lastRow).Value
 Me.lstDatabase.list = arr10
End Sub