jag108
Active Member
- Joined
- May 14, 2002
- Messages
- 433
- Office Version
- 365
- 2016
- Platform
- Windows
- MacOS
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.
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.