Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill combo box ignoring blanks
I have code to fill a combo box from a selected range, but it also picks up
any blank cells interspersed in that range. Is there a way to tell it to ignore blanks? This is my code (OpenWS and Lr were declared earlier): With Me.cboPO ..Clear ListItems = OpenWS.Range("D7:D600").Value ListItems = Application.WorksheetFunction.Transpose(ListItems) ' convert values to a vertical array For i = 1 To Lr .AddItem ListItems(i) ' populate the listbox Next i .ListIndex = -1 ' no items selected, set to 0 to select the first item End With Thanks for your help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill combo box ignoring blanks
With Me.cboPO
..Clear ListItems = OpenWS.Range("D7:D600").Value For i = 1 To Ubound(ListItems,1) if len(trim(listItems(i,1))) 0 then .AddItem ListItems(i,1) ' populate the listbox end if Next i .ListIndex = -1 ' no items selected, set to 0 to select the first item End With -- Regards, Tom Ogilvy "Shannon" wrote: I have code to fill a combo box from a selected range, but it also picks up any blank cells interspersed in that range. Is there a way to tell it to ignore blanks? This is my code (OpenWS and Lr were declared earlier): With Me.cboPO .Clear ListItems = OpenWS.Range("D7:D600").Value ListItems = Application.WorksheetFunction.Transpose(ListItems) ' convert values to a vertical array For i = 1 To Lr .AddItem ListItems(i) ' populate the listbox Next i .ListIndex = -1 ' no items selected, set to 0 to select the first item End With Thanks for your help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill combo box ignoring blanks
Thank you so much! Works woderfully!
"Tom Ogilvy" wrote: With Me.cboPO .Clear ListItems = OpenWS.Range("D7:D600").Value For i = 1 To Ubound(ListItems,1) if len(trim(listItems(i,1))) 0 then .AddItem ListItems(i,1) ' populate the listbox end if Next i .ListIndex = -1 ' no items selected, set to 0 to select the first item End With -- Regards, Tom Ogilvy "Shannon" wrote: I have code to fill a combo box from a selected range, but it also picks up any blank cells interspersed in that range. Is there a way to tell it to ignore blanks? This is my code (OpenWS and Lr were declared earlier): With Me.cboPO .Clear ListItems = OpenWS.Range("D7:D600").Value ListItems = Application.WorksheetFunction.Transpose(ListItems) ' convert values to a vertical array For i = 1 To Lr .AddItem ListItems(i) ' populate the listbox Next i .ListIndex = -1 ' no items selected, set to 0 to select the first item End With Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Criteria average ignoring blanks | Excel Discussion (Misc queries) | |||
Criteria average ignoring blanks | Excel Discussion (Misc queries) | |||
Ignoring blanks from Column(s) | Excel Worksheet Functions | |||
Ignoring blanks and consolidating | Excel Discussion (Misc queries) | |||
Ignoring blanks | Excel Discussion (Misc queries) |