Populate Combobox from sheet with criteria

[sharky12345]

Is there a way I can populate a Combobox on a Userform with values from column A but only if the cell in Column C is NOT blank?

 

[mikerickson]

This should work

Dim oneCell As Range

With Sheet1.Range("A:A")
    For Each oneCell In Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
        With oneCell
            If CStr(.Offset(0, 2).Value) <> vbNullString Then
                ComboBox1.AddItem CStr(.Value)
            End If
        End With
    Next oneCell
End With

 

[Rick Rothstein]

If your values in Column C are constants (that is, the cell values are not produced by formulas), then you can use this code from within the UserForm's module...

  Dim Cell As Range

  For Each Cell In Sheets("Sheet1").Columns("C").SpecialCells(xlConstants).Offset(, -2)
    ComboBox1.AddItem Cell.Value
  Next

 

[sharky12345]

Thanks guys for both suggestions!

 

[Rick Rothstein]

If your values in Column C are constants (that is, the cell values are not produced by formulas), then you can use this code from within the UserForm's module...

  Dim Cell As Range

  For Each Cell In Sheets("Sheet1").Columns("C").SpecialCells(xlConstants).Offset(, -2)
    ComboBox1.AddItem Cell.Value
  Next

I do not recommend using the following code, but I know that there are some out "there" who like to see my one-liners, so the following is for "entertainment purposes" only...

ComboBox1.List = Split(Application.Trim(Join(Application.Transpose(Evaluate(Replace("IF(LEN([COLOR=#0000FF][B]Sheet1[/B][/COLOR]!C1:C#),[COLOR=#0000FF][B]Sheet1[/B][/COLOR]!A1:A#,"""")", "#", Sheets("[COLOR=#0000FF][B]Sheet1[/B][/COLOR]").Cells(Rows.Count, "C").End(xlUp).Row))))))

 

[sharky12345]

Blimey Rick - I will use it purely for entertainment!