How to remove empty cells from Listbox

  • Thread starter Thread starter Corey
  • Start date Start date
C

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....
 
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....
Click to expand...

Hello Corey,

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

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

Sincerely,
Leith Ross
 
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....
Click to expand...

Hello Corey,

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

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

Sincerely,
Leith Ross
 
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.
 
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.
 
Back
Top