Solved: Skip Blank Cells

[belly0fdesir]

I'm sorry if this is a dumb question... I've searched through the KB to no avail, and I don't know if this is even possible... it's going to be pretty hard to explain:

I have a combo box that is tied to a range of cells... but this list changes, based on the contents of another combo box. The list shows the people that are out for a particular department. The user can click the department combo box and select a different department, which will change the list, which changes what they view when they click on the first combo box that I mentioned.

Right now the combo box that lists the people out works fine, except that there are a lot of blank spots between people. So my goal is to get rid of those blank spots.

Is there a way for Column B to show only the contents of Column A that are not blank? If there is then I could tie the combo box to Column B and my problem would be solved.

Please let me know if you have a solution to this problem, or if this even makes sense... Thanks!

[Simon Lloyd]

You could try this
[VBA]
Sub Macro1()
Range("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
"B:B"), Unique:=True
Columns("B:B").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub
[/VBA]this filters unique values in column A to column B then sorts them.

Hope it helps,
Regards,
Simon

[belly0fdesir]

I'll have to just tweak it so that it's executed on the change of the first combo box, but that should work perfectly. Thank you very much and consider this solved!