Combobox additem

[jag108]

I have a combo box which is populated via a for loop, from data on a work sheet. The problem I am having is that there are a number of empty entries in the top of the combo box, so you have to scroll down about 10 or so before you can see any data.

Code:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Activate()
<SPAN style="color:#00007F">Set</SPAN> src_sht = Worksheets("Channel Details")
src_sht.Select
<SPAN style="color:#00007F">Set</SPAN> TestRange = Intersect(Range("B:B"), ActiveSheet.UsedRange)
lastrow = TestRange.Cells(TestRange.Cells.Count).Row <SPAN style="color:#007F00">'get last row number in the range</SPAN>
firstrow = TestRange.Cells(1).Row <SPAN style="color:#007F00">'get the first row number in the range</SPAN>
<SPAN style="color:#00007F">With</SPAN> Channel_events.ComboBox1
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> src_sht.Range(Cells(2, 3), Cells(lastrow, 3))
        <SPAN style="color:#00007F">If</SPAN> cell.Offset(0, 1).Value = <SPAN style="color:#00007F">Empty</SPAN> <SPAN style="color:#00007F">Or</SPAN> cell.Offset(0, 1).Value = " " <SPAN style="color:#00007F">Then</SPAN>
        
        <SPAN style="color:#00007F">Else</SPAN>
            .AddItem cell.Value
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Worksheets("Events").Select
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

What this does is look at the adjacent cell if it is empty then do nothing, other wise populate the combo box with the data.

 

[jindon]

Hi
change

With Channel_events.ComboBox1
    For Each cell In src_sht.Range(Cells(2, 3), Cells(lastrow, 3))
        If Not IsEmpty(cell) And Not IsEmpty(cell.Offset(0, 1)) Then
            .AddItem cell.Value
        End If
    Next
End With

 

[jag108]

Hi,

I tried that but it failed, I have also tried your code but failure again.

The data is in cols b and c, and has blanks in the C column, which I want to exclude.

 

[jindon]

Confusing

Which column are you adding to the combobox under what condition?

 

[jag108]

OK - I will try and explain a little better.
I have TV channels that I need to extract data for, the chanel codes are in another sheet so this is used to ppopulate the combo box.

IBMS_CA_Template_Parameters.xls
	B	C	D	E
1	CHANNEL_NAME	TRANSMISSION_CODE	SI_SERVICE_KEY	CHANNEL_SHORT_CODE
2	APromoPriorities		0	A8
3	ASPORTSPromoPriorities		0	A9
4	AnimalPlanet	ANIM	707	15
5	ApnaFM	APNA	612	87
6	BBCWorld	BBCV	704	61
7	BusinessLink		508	BL
8	CalvaryChapel	CCRC	424	96
9	CartoonNetwork	CRTN	302	C1
10	CCTV9	CCTV	208	C2
11	Ch65		0	75
12	Channel1		0	CH1
13	ChannelZ	EDGE	130	60
14	CNNIntDBS	CNNS	705	06
15	ConcertFM	RNZ2	322	52
16	Discovery	DISC	204	08
17	DiscoveryUHF		800	28
18	DisneyChannel	DISN	708	98
19	E	ECHL	207	E2
20	ESPNInternational	ESPN	505	12
Channel Details

The &nbsp: are blank cells, cells that are not wanted I only want the cells with data in the combo box. I hope this helps.

 

[jindon]

Sorry, but I don't think you are answering to my question.

Which column are you adding to the combobox?

 

[jag108]

Sorry column "C"

 

[jindon]

Is this what you mean?

If Not IsEmpty(cell) And Not IsEmpty(cell.Offset(, -1)) Then
            .AddItem cell.Value
        End If

 

[jag108]

No this is still picking up the blanks as well.

 

[jindon]

Private Sub UserForm_Activate()

Set src_sht = Worksheets("Channel Details")
src_sht.Select
Set testrange = Intersect(Range("C:C"), ActiveSheet.UsedRange)
lastrow = testrange.Cells(testrange.Cells.Count).Row 'get last row number in the range
firstrow = testrange.Cells(1).Row 'get the first row number in the range
With Channel_events.ComboBox1
    For Each cell In src_sht.Range(Cells(2, 3), Cells(lastrow, 3))
        If Not IsEmpty(cell) And Not IsEmpty(cell.Offset(, -1)) Then
            .AddItem cell.Value
        End If
    Next
End With
Worksheets("Events").Select
End Sub