0

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?

Wolfie
  • 27,562
  • 7
  • 28
  • 55
canerkorkmaz
  • 39
  • 7

2 Answers2

1

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
VBA Pete
  • 2,656
  • 2
  • 24
  • 39
  • @wolfie: does ActiveSheet.UsedRange.Rows.Count include empty cells in the count? – VBA Pete Jan 10 '17 at 17:17
  • Yes, `UsedRange` can be a bit temperamental because it sometimes "remembers" a bigger range than you actually now use! However, it goes from "A1" (assuming some cell in row 1 and some cell in column A once had anything in them) to the bottom-right ever-used cell. Play around and see here for more: http://stackoverflow.com/questions/7423022/getting-the-actual-usedrange – Wolfie Jan 10 '17 at 17:33
  • 1
    I don't think i can thank you enough for this perfect answer.Saved me lots of time.Thank you. – canerkorkmaz Jan 10 '17 at 17:38
  • Not a problem @canerkorkmaz. – VBA Pete Jan 10 '17 at 17:43
0

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
Cakadola
  • 1