Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore blank rows to populate custom drop down list column range
I am creating a custom form in an Excel worksheet using VBA and want a "List
box" but not all rows in the column I reference have entries and is several hundred rows long and subject to change. It shows all rows in list even blank ones in that column forcing you to scroll through several blanks and even multiple rows showing same thing. Basically I would like it populated similar to the auto filter function from you can use from the main sheet, no blanks and one entry per word no matter how many times it shows up. Is this an easy property settings function or does it require a degree in VBA programming? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore blank rows to populate custom drop down list column range
You need to create a list without the blanks, or load it manually. For
example Private Sub UserForm_Activate() Dim rng As Range Dim rng2 As Range Dim cell As Range Set rng = Range("A1:A1000").SpecialCells(xlCellTypeConstants ) On Error Resume Next Set rng2 = Range("A1:A1000").SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If Not rng2 Is Nothing Then Set rng = Union(rng, rng2) End If For Each cell In rng Me.ComboBox1.AddItem cell.Value Next cell End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "vidtec" wrote in message ... I am creating a custom form in an Excel worksheet using VBA and want a "List box" but not all rows in the column I reference have entries and is several hundred rows long and subject to change. It shows all rows in list even blank ones in that column forcing you to scroll through several blanks and even multiple rows showing same thing. Basically I would like it populated similar to the auto filter function from you can use from the main sheet, no blanks and one entry per word no matter how many times it shows up. Is this an easy property settings function or does it require a degree in VBA programming? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore blank rows to populate custom drop down list column ran
Thank you for your help. I was afraid that this was not going to be as easy as changing a "0" to "1" or something similar in the properties settings. I will play around with your suggestion and see if I can get to work in my situation. Thank you so much for your time it is appreciated. Next year after Vista and Office 12 comes out I plan on upgrading to Access. I am trying to do too much in Excel with inventory control with to many Items that have too much information per item, I think I need to upgrade to a database and create reports & forms. Excel is great but just won't go through the hoops I need it to anymore. Thanks again.. :-) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignore blank rows to populate custom drop down list column ran
It will, you just need to know how. You will have the same problem in
Access, with a much less rich environment IMO. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "vidtec" wrote in message ... Thank you for your help. I was afraid that this was not going to be as easy as changing a "0" to "1" or something similar in the properties settings. I will play around with your suggestion and see if I can get to work in my situation. Thank you so much for your time it is appreciated. Next year after Vista and Office 12 comes out I plan on upgrading to Access. I am trying to do too much in Excel with inventory control with to many Items that have too much information per item, I think I need to upgrade to a database and create reports & forms. Excel is great but just won't go through the hoops I need it to anymore. Thanks again.. :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT - Ignore blank rows | Excel Worksheet Functions | |||
Populate a column of data with a formula using a drop down list | Excel Discussion (Misc queries) | |||
Using a drop-down list along with an auto-populate drop-down | Excel Discussion (Misc queries) | |||
Drop Down List Ignore Blank | Excel Discussion (Misc queries) | |||
How do I set up a drop down validation to ignore any blank cells? | Excel Discussion (Misc queries) |