Sleeper: Combo Box Range problem

[Rozzzer]

Hi, I have created a range in Excel which selects all of the blank cells in column 5 of another range. I now want to use an offset of (0,-4) in this range as the rowsource in a combo box. Unfortunatly I'm not having much success at the moment:


Dim bob As Range
Dim a As Range
Set a = Range("a1").CurrentRegion
Set bob = a.Columns(5).SpecialCells(xlCellTypeBlanks).Offset(0, -4)
bob.Select
cboPatientName.RowSource = bob.Address

bob is certainly the range that I want but no luck in getting it to appear in the combo box. Any help would be appreciated even if to say it is not possible this way

[Jacob Hilderbrand]

It looks like the multiple sections in the range were messing up the row source. Try this:


Dim bob As Range
Dim a As Range
Dim cel As Range
Set a = Range("a1").CurrentRegion
Set bob = a.Columns(5).SpecialCells(xlCellTypeBlanks).Offset(0, -4)
For Each cel In bob
Me.ComboBox1.AddItem (cel.Text)
Next

[Jacob Hilderbrand]

I also editted your post to use VBA tags. Notice how the code looks like actual code now. To use the tags write this when you post:



Code Here


When you really do this replace the parentheses with brackets [].