Excluding NULL values in an Excel List

I have a listbox in an Excel Worksheet. The listbox is at position B1(eg).
The data range for the listbox is from A(1:1000). But all the cells A(1:1000)
may or may not have data. How can i filter the data in my listbox, so that it
does not have any NULL values(blank data).

Use additem in your code to populate the listbox rather than using
ListfillRange. Then only add cells that are not empty.

Dim cell as Range
for each cell in Range("A1:A1000")
if not isempty(cell) then
Activesheet.Listbox1.AddItem Cell.Text
end if
Next

--
Regards,
Tom Ogilvy

"Sheri" <[email protected]> wrote in message
news:[email protected]...
> I have a listbox in an Excel Worksheet. The listbox is at position B1(eg).
> The data range for the listbox is from A(1:1000). But all the cells
A(1:1000)
> may or may not have data. How can i filter the data in my listbox, so that
it
> does not have any NULL values(blank data).

HI,

that would work if there was only 1 listbox. let me re-phrase my question. i
need an entire column with listboxes say B(1:10000) which loads the data from
Column A(1:1000) without blanks at the end or anywhere in between,
considering that A(1:1000) has blanks in between and at the end.

Regards
Sheri


"Tom Ogilvy" wrote:

> Use additem in your code to populate the listbox rather than using
> ListfillRange. Then only add cells that are not empty.
>
> Dim cell as Range
> for each cell in Range("A1:A1000")
> if not isempty(cell) then
> Activesheet.Listbox1.AddItem Cell.Text
> end if
> Next
>
> --
> Regards,
> Tom Ogilvy
>
> "Sheri" <[email protected]> wrote in message
> news:[email protected]...
> > I have a listbox in an Excel Worksheet. The listbox is at position B1(eg).
> > The data range for the listbox is from A(1:1000). But all the cells
> A(1:1000)
> > may or may not have data. How can i filter the data in my listbox, so that
> it
> > does not have any NULL values(blank data).
>
>
>

Let me re-phrase my answer. There is no built in capability to assign a
fixed range (or otherwise) as teh ListFillRange and have the listbox only
display non-blank entries. (regardless of how many listboxes you might have)


As an alternative, if the blanks all occur after the last used cell, then
you could create a dynamic range by doing

Insert=>Name=>Define

Name: List1
RefersTo: =Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A$1:$A$1000),1)

then in the listfillrange property, use the defined name.

You will have to force a full recalc to refresh the listboxes if you add or
remove date from the range and you should avoid inserting and deleting rows.

--
Regards,
Tom Ogilvy


"Sheri" <[email protected]> wrote in message
news:[email protected]...
> HI,
>
> that would work if there was only 1 listbox. let me re-phrase my question.
i
> need an entire column with listboxes say B(1:10000) which loads the data
from
> Column A(1:1000) without blanks at the end or anywhere in between,
> considering that A(1:1000) has blanks in between and at the end.
>
> Regards
> Sheri
>
>
> "Tom Ogilvy" wrote:
>
> > Use additem in your code to populate the listbox rather than using
> > ListfillRange. Then only add cells that are not empty.
> >
> > Dim cell as Range
> > for each cell in Range("A1:A1000")
> > if not isempty(cell) then
> > Activesheet.Listbox1.AddItem Cell.Text
> > end if
> > Next
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Sheri" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a listbox in an Excel Worksheet. The listbox is at position
B1(eg).
> > > The data range for the listbox is from A(1:1000). But all the cells
> > A(1:1000)
> > > may or may not have data. How can i filter the data in my listbox, so
that
> > it
> > > does not have any NULL values(blank data).
> >
> >
> >