Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excluding NULL values in an Excel List
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" wrote in message ... 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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excluding NULL values in an Excel List
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" wrote in message ... 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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excluding NULL values in an Excel List
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" wrote in message ... 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" wrote in message ... 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find and replace null values in Excel 2002 worksheets? | Excel Worksheet Functions | |||
CSV export from Excel always recognise null values in the last col | Excel Discussion (Misc queries) | |||
VB excel Null values !! Please help me !! | Excel Programming | |||
While reading excel file i am getting null values | Excel Programming | |||
While reading excel file i am getting null values | Excel Programming |