combobox, how do i exclude blanks from list retrieved via listobject

user_5719255 0
Viewed 432

Trying to apply data to a combobox, which works out great, except it also include filtered values.

  1. i filter on field 1, filter by a number, there are several empty cells in this row, those with empty cells in field1 i dont want to see this time.

  2. I pupulate the databodyrange value from column 13 into the combobox list, however even when filtered correctly it also adds the rows i filtered away.

code..

Private Sub UserFrom_Initialize()
  Dim db As ListObject
  Set db = Worksheets("baseOfData").ListObjects("database")

  db.Range.AutoFilter Field:=1, Criteria1:="<>"
  Me.cmbTasks.List = db.ListColumns(13).DataBodyRange.Value
End Sub

I can solve it by running a for loop, and checking every cell before adding it but that would kinda defeat the purpose of doing it all with 2 lines of code.

any suggestions

vba excel
2 Answers

however even when filtered correctly it also adds the rows i filtered away.

Me.cmbTasks.List = db.ListColumns(13).DataBodyRange.Value

That is because you are incorrectly doing it. You are referring to complete column and not the filtered range. Try this

Dim db As ListObject
Set db = Worksheets("baseOfData").ListObjects("database")

db.Range.AutoFilter Field:=1, Criteria1:="<>"
Me.cmbTasks.List = db.DataBodyRange.Columns(13).SpecialCells(xlCellTypeVisible).Value

The next problem that you may face will be that it will show values from the first Area only if there are multiple areas.

enter image description here

To handle this, try

Dim db As ListObject
Dim aCell As Range, rngArea As Range

Set db = Worksheets("baseOfData").ListObjects("database")

db.Range.AutoFilter Field:=1, Criteria1:="<>"

'~~> Loop through each area
For Each rngArea In db.DataBodyRange.Columns(13).SpecialCells(xlCellTypeVisible).Areas
    '~~> Loop though each cell in the area
    For Each aCell In rngArea
        cmbTasks.AddItem aCell.Value
    Next aCell
Next rngArea

enter image description here

user_11405790

Why not a little simpler (if iteration is an option):

Dim db As ListObject, cel As Range

Set db = Worksheets("baseOfData").ListObjects("database")
For Each cel In db.DataBodyRange.Columns(13).Cells
    If cel.EntireRow.Hidden <> True Then
        cmbTasks.AddItem cel.value
    End If
Next

Would you like to do some modifications on the table, according to selected combo value? If yes, the real row number associated to each combo value must be memorized. In a hidden column of the combo, for instance (using cel.Row).

user_22333080
Related