How do I Remove Blanks from a UserForm ComboBox

[coreyalaurence39]

I am trying to populate a UserForm ComboBox drop down list and it is also picking up the blanks that are in the column. How do I remove the blanks in the ComboBox on the UserForm. I have not found any code that has worked for this issue.

 

[JEC]

When the data is in column A

Private Sub UserForm_Initialize()
 With Range("A1", Range("A" & Rows.Count).End(xlUp))
   ComboBox1.List = Filter(Application.Transpose(Evaluate("if(" & .Address & "<>""""," & .Address & ")")), False, 0)
 End With
End Sub

or

Private Sub UserForm_Initialize()
Range("A1", Range("A" & Rows.Count).End(xlUp)).Name = "ar"
ComboBox1.List = Filter([Transpose(if(ar<>"",ar))], False, 0)
End Sub

 

[coreyalaurence39]

When the data is in column A

Private Sub UserForm_Initialize()
 With Range("A1", Range("A" & Rows.Count).End(xlUp))
   ComboBox1.List = Filter(Application.Transpose(Evaluate("if(" & .Address & "<>""""," & .Address & ")")), False, 0)
 End With
End Sub

or

Private Sub UserForm_Initialize()
Range("A1", Range("A" & Rows.Count).End(xlUp)).Name = "ar"
ComboBox1.List = Filter([Transpose(if(ar<>"",ar))], False, 0)
End Sub

Thank you that is helpful. I apologize for not mentioning this but the data is actually in column B.

 

[JEC]

Then change A to B

 

[coreyalaurence39]

Thank you that is helpful. I apologize for not mentioning this but the data is actually in column B.

Then change A to B

One More quick question I need to pull the data from a worksheet name Employee_Master. Can you tell me how to alter this code to point to that sheet?

 

[JEC]

For example:

Private Sub UserForm_Initialize()
Sheets("Employee_Master").Range("A1", Sheets("Employee_Master").Range("A" & Rows.Count).End(xlUp)).Name = "ar"
ComboBox1.List = Filter([Transpose(if(ar<>"",ar))], False, 0)
End Sub

 

[coreyalaurence39]

For example:

Private Sub UserForm_Initialize()
Sheets("Employee_Master").Range("A1", Sheets("Employee_Master").Range("A" & Rows.Count).End(xlUp)).Name = "ar"
ComboBox1.List = Filter([Transpose(if(ar<>"",ar))], False, 0)
End Sub

Thank you that is working just like I want it to

Do I have two ComboBoxes that I need to populate with different data. The other data is on a sheet called Earning_Codes. Do I just use this same code and alter it for that sheet and include it in the Private Sub UserForm Initialize?

 

[JEC]

Yes that should work indeed

 

[coreyalaurence39]

Yes that should work indeed

I just do not know how to combine the two sets of code for the two separate sheets.