Ignore Blank Cells in Drop Down List

[Subbie]

Hi Following some very useful help yesterday I was able to plough on with my project.
I am now on my final UserForm and have a combobox2 which is coded:

Private Sub ComboBox2_DropButt*******()
With ComboBox2
ComboBox2.List = Worksheets("BankRec").Range("A1:A175").Value
End With
End Sub
This works great however There are blank cells at the beginning of the list that will always be in the column dropdown list and I would like the blank cells to be ignored so that the dropdown list starts with the first populated cell. I have tried various methods found on the web and tried a few of my own but I got compile errors or range errors.
Could anyone suggest a way to get the drop box to go to the first filled cell and ignore blank?
As always grateful for any help.
Subbie

 

[Fluff]

Are the values in col A unique?

 

[Fluff]

If you're happy with unique values only, try

Private Sub ComboBox2_DropB[COLOR=#000000]utt[/COLOR][COLOR=#000000]*******[/COLOR]()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Worksheets("BankRec").Range("A1:A175")
         If Not Cl = "" Then .Item(Cl.Value) = Empty
      Next Cl
      Me.ComboBox2.List = .Keys
   End With
End Sub

 

[Subbie]

Are the values in col A unique?

Hi
Thanks for the response.
The setup is that the References are in Column A: CD1, CD2 etc.
When the user uses the Ref i.e. CD1 it is written to column G, CD1 is then removed from Column A. The way the deletion occurs means the ref CD1 remains in Column B which is a control column. So that means the Reference appears in 2 columns.

Subbie

 

[Subbie]

Hi
Thanks for the response.
The setup is that the References are in Column A: CD1, CD2 etc.
When the user uses the Ref i.e. CD1 it is written to column G, CD1 is then removed from Column A. The way the deletion occurs means the ref CD1 remains in Column B which is a control column. So that means the Reference appears in 2 columns.

Subbie

Was getting an error and realised it was my changing another variable.
Now working exactly as I need it to.
Very grateful for your wise advice.
Subbie

 

[Fluff]

You're welcome & thanks for the feedack