Range into ListBox excluding blanks

[KIM W]

I successfully populate a listbox with a list of values based on the value
selected in a combox. The value in the combobox is a named range. However,
that list may have blanks in it that I don't want.
How can I display the listing in the listbox excluding any blanks so it is
one solid list? THe total items is less than 200, and often less than 20.

Private Sub ComboBox1_Change()
Dim rng As Variant
'rng = UserForm1.ComboBox1.Selected(x)
With ActiveSheet
rng = UserFormListToCell.ComboBox1.Value
UserFormListToCell.ListBox1.RowSource = rng
End With

End Sub

 

[Dave Peterson]

Loop through the cells in that range and check to see if they're empty. Use
..additem to add them to the listbox.

Option Explicit
Private Sub ComboBox1_Change()

Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
With ActiveSheet
Set myRng = .Range(Me.ComboBox1.Value)
End With
On Error GoTo 0

Me.Label1.Caption = ""

If myRng Is Nothing Then
Beep
Me.Label1.Caption = "Select a name from the combobox"
Else
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'skip it
Else
Me.ListBox1.AddItem myCell.Value
End If
Next myCell
End If

End Sub
Private Sub UserForm_Initialize()
With Me.ComboBox1
.Clear
.AddItem "rngname1"
.AddItem "rngname2"
End With
End Sub

 

[Dave Peterson]

ps. The me keyword is the object that owns the code. In the sample code I
posted, it was the userform itself.

By using that keyword, I don't have to worry about the name of the userform. (I
did assume that it was a single userform--not that you were controlling two???)

 

[KIM W]

Dear Dave,
Thanks so much for the code solution for creating range excluding blanks.
It works when the range is on the same worksheet that I launch the Userform
from. Could you assist further by showing me how the rage can be from
another worksheet?
A related question, please: How can I make this new blank-less range also
the range I use in a Validation List?
Here's what I ended up with:

Option Explicit
Private Sub ComboBox1_Change()

Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
With ActiveSheet
Set myRng = .Range(Me.ComboBox1.Value)
End With
On Error GoTo 0

Me.Label1.Caption = ""

If myRng Is Nothing Then
Beep
Me.Label1.Caption = "Select a name from the combobox"
Else
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'skip it
Else
Me.ListBox1.AddItem myCell.Value
End If
Next myCell
End If
End Sub

Private Sub UserForm_Initialize()

Dim nName As Name
Dim myNames() As String
Dim iCtr As Long

iCtr = 0
For Each nName In ThisWorkbook.Names
If LCase(nName.Name) Like LCase("List*") Then
iCtr = iCtr + 1
ReDim Preserve myNames(1 To iCtr)
myNames(iCtr) = nName.Name
End If
Next nName

If iCtr = 0 Then
UserFormListToCell.ComboBox1.Enabled = False
Else
With UserFormListToCell.ComboBox1
..Clear
..List = myNames
..Enabled = True

 

[Dave Peterson]

If you know the name of the sheet, you can change this line:

With ActiveSheet
to
With Worksheets("sheetnamehere")

======
If I wanted to use a range that contained blanks as my list in data|validation,
I'd copy it to a new range. Sort it and use the non-empty range.

Or maybe just sort the original range (all the columns!) and use a dynamic range
name that grew or contracted with the amount of data.

Debra Dalgleish explains dynamic range names here:
http://contextures.com/xlNames01.html#Dynamic

(Maybe that's what you could use for populating the listbox, too???)

 

[KIM W]

Well, I don't know the name of the worksheet that contains the range. I have
about 100 worksheets, each one has in it a range in which the user enters a
list. Later we want those values in listboxes accessible on various other
worksheets. All was working well, but for the blanks. (I was using
..rowsource.)

If this is getting too specific, jut let me know. You've been very helpful
already.

And, yes, this workbook solution already has Debra Dalgleish dynamic list
box in it-- very valuable! I find her web sit offers numerous good solutions
that are compatible with my workbook development style.

 

[Dave Peterson]

Then you could go through the Names collection:

With ActiveSheet
Set myRng = .Range(Me.ComboBox1.Value)
End With

Becomes
Set myRng = activeworkbook.names(Me.ComboBox1.Value).referstorange

Well, I don't know the name of the worksheet that contains the range. I have
about 100 worksheets, each one has in it a range in which the user enters a
list. Later we want those values in listboxes accessible on various other
worksheets. All was working well, but for the blanks. (I was using
.rowsource.)

If this is getting too specific, jut let me know. You've been very helpful
already.

And, yes, this workbook solution already has Debra Dalgleish dynamic list
box in it-- very valuable! I find her web sit offers numerous good solutions
that are compatible with my workbook development style.