How to remove empty cells from Listbox

[Corey]

Private Sub UserForm_Initialize()
With ListBox1
..AddItem Range("N1")
..AddItem Range("N2")
..AddItem Range("N3")
..AddItem Range("N4")
..AddItem Range("N5")
..AddItem Range("N6")
..AddItem Range("N7")
..AddItem Range("N8")
..AddItem Range("N9")
..AddItem Range("N10")
..AddItem Range("N11")
..AddItem Range("N12")
End With
End Sub

Also is there a way to Addityem in a Range("N1:N12") ??


Corey....

 

[Leith Ross]

Private Sub UserForm_Initialize()
With ListBox1
.AddItem Range("N1")
.AddItem Range("N2")
.AddItem Range("N3")
.AddItem Range("N4")
.AddItem Range("N5")
.AddItem Range("N6")
.AddItem Range("N7")
.AddItem Range("N8")
.AddItem Range("N9")
.AddItem Range("N10")
.AddItem Range("N11")
.AddItem Range("N12")
End With
End Sub

Also is there a way to Addityem in a Range("N1:N12") ??

Corey....

Hello Corey,

Use the List property to load the range into the ListBox.

Example:
ListBox1.List() = Range("N1:N12").Value

Sincerely,
Leith Ross

 

[Corey]

Thanks Leith

How can i not load in cells in that range that have NO value (='"") ?

Private Sub UserForm_Initialize()
With ListBox1
.AddItem Range("N1")
.AddItem Range("N2")
.AddItem Range("N3")
.AddItem Range("N4")
.AddItem Range("N5")
.AddItem Range("N6")
.AddItem Range("N7")
.AddItem Range("N8")
.AddItem Range("N9")
.AddItem Range("N10")
.AddItem Range("N11")
.AddItem Range("N12")
End With
End Sub

Also is there a way to Addityem in a Range("N1:N12") ??

Corey....

Hello Corey,

Use the List property to load the range into the ListBox.

Example:
ListBox1.List() = Range("N1:N12").Value

Sincerely,
Leith Ross

 

[Dave Peterson]

Option Explicit
Private Sub UserForm_Initialize()

dim myCell as range
dim myRng as range

set myrng = worksheets("sheet9999").range("n1:N12")

With ListBox1
for each mycell in myrng.cells
if trim(mycell.value) = "" then
'skip it
else
.additem mycell.value
end if
next mycell
end with
End Sub

Uncompiled. Untested. Watch for typos.

 

[Corey]

PERFECT thank you dave


Option Explicit
Private Sub UserForm_Initialize()

dim myCell as range
dim myRng as range

set myrng = worksheets("sheet9999").range("n1:N12")

With ListBox1
for each mycell in myrng.cells
if trim(mycell.value) = "" then
'skip it
else
.additem mycell.value
end if
next mycell
end with
End Sub

Uncompiled. Untested. Watch for typos.