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?

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?

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.. :-)

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.. :-)