Userform: Dynamic listbox to exclude cells with blank formula

[Brett Scott]

Hello,

I currently am trying to populate a listbox based off a combobox selection. my setup is currently this:

Sheets("Sheet1").range("A1") = Combobox1.value

i then have an array formula in B3 that equals=
{=SMALL(IF(AgentInfo[Unit]=$A$1,ROW(AgentInfo[Unit])),ROW(1:1))}

from this formula in cell B3, i have an index, Match formula in row C3 that starts matching info to populate a list.. all based off the combobox value shown in A1=
=IFERROR(INDEX(AgentInfo[Unit],MATCH(B3,AgentInfo[Row'#],0)),"-")

This formula is then dragged down to row 75. This formula will populate different data based on A1.

Now im trying to populate the listbox and the listbox is capturing all the rows that include "-" instead of just the data.

how would i exclude the "-"'s in the rows?

here is my current formula:

 With Sheets("Attendance")
 
ListBox2.RowSource = Range(.Range("D3:E3"), .Cells(Rows.Count, "D").End(xlUp)).Address(, , , True)
 
  End With

 

[NARAYANK991]

Hi Brett ,

You can create a named range which excludes the cells without data , and then use the named range as the RowSource of the listbox.

Narayan

 

[Smallman]

Hi Brett

Alternatively you could add an advanced filter line to your code, store the data in another location and populate the list box from there. Here is an example.

Sub MoveMe()
Range("A1", Range("A" & Rows.Count).End(xlUp))AdvancedFilter xlFilterCopy, [B1:B2], [H1]
  With Sheet1
        ListBox1.List = Application.Transpose(.Range(.Range("H2"), .Range("H2").End(xlDown)))
    End With
End Sub

Will attach a file to show workings.

Take care

Smallman