CODEWITHSUNDEEP
vbaexcellistbox
canerkorkmaz
canerkorkmaz

Reputation: 39

Eliminating blank cells in listbox rowsource

I am trying to make a listbox that eliminates blank cells on rows. In column A I have some cells which contain data and some which are blank. I can't delete the blank rows because in other columns they contain data. How can I make the non-blank cells the rowsource in a listbox?

Upvotes: 0

Views: 2751

Answers (2)

Cakadola
Cakadola

Reputation: 1

To Avoid Duplicates, please use this additional code below:

    ' If the cell is not blank then add it as a list item
        If ActiveSheet.Range("A" & x).Value <> "" Then
          ' Avoid Duplicates   
             If ActiveSheet.Range("A" & x) <> ActiveSheet.Range("A" & x).Offset(-1, 0).Value 
            Then

     .AddItem ActiveSheet.Range("A" & x).Value

    End If
    End If

Upvotes: 0

VBA Pete
VBA Pete

Reputation: 2666

How about a loop that checks if there is a value in each cell:

Dim CountLng as Long

'Set CountLng to maximum row in column A that you would like to search for.
'This example uses the number of rows in the entire used range of the worksheet

CountLng = ActiveSheet.UsedRange.Rows.Count

With listbox1

    ' Loop over each cell in the column A 
    For x = 1 To CountLng

        ' If the cell is not blank then add it as a list item
        If ActiveSheet.Range("A" & x).Value <> "" Then

            .AddItem ActiveSheet.Range("A" & x).Value

        End If

    Next x

End With

Upvotes: 1

Related Questions