How to remove empty cells from Listbox
 

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....

How to remove empty cells from Listbox
 

On May 10, 4:42 pm, "Corey" wrote:
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

How to remove empty cells from Listbox
 

Thanks Leith

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


"Leith Ross" wrote in message
ps.com...
On May 10, 4:42 pm, "Corey" wrote:
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

How to remove empty cells from Listbox
 

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 wrote:

Thanks Leith

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

"Leith Ross" wrote in message
ps.com...
On May 10, 4:42 pm, "Corey" wrote:
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

How to remove empty cells from Listbox
 

PERFECT thank you dave


"Dave Peterson" wrote in message
...
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 wrote:

Thanks Leith

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

"Leith Ross" wrote in message
ps.com...
On May 10, 4:42 pm, "Corey" wrote:
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