How to remove blank items from listbox?

[Connect]

hi i have qusetion
how to remove ongy blank items from listbox?
I mean I wanna make button that only remove blank items from listbox

[Oralloy]

Connect,

Just loop over the elements in the list, and if any is blank, remove it.

Dim l As MSForms.ListBox
Dim i As Long: i = 0
While i < l.ListCount
 If "" = l.List(i, 0) Then: l.RemoveItem (i): Else i = 1 + i
Wend

Cheers!
Oralloy

[Rabbit]

I'm assuming the data is coming from a database. You could just not return the blank rows when retrieving the data from the database.

[Connect]

didnot work

[Connect]

didnot work orally

[Oralloy]

Connect,

Frist off, I assume that you set the variable l to your list box, like this:

Set l = Me.ListBox1

Also, if your "blank" items are strings of blanks, you'll have to use Trim$ to remove the extra spaces:

Dim l As MSForms.ListBox 
Set l = Me.ListBox1

Dim i As Long: i = 0

While i < l.ListCount 
 If "" = Trim$(l.List(i, 0)) Then: l.RemoveItem (i): Else i = 1 + i 
Wend

So - did you see any errors, and where did you put the code?

[cpanelxp]

I have same problom Oralloy
and your code didnt work with me
could you attach editale file if this code

[Oralloy]

cpanelxp,

Here is a fully functional demonstration of a list and code to clean it.

Luck!
Oralloy

[cpanelxp]

my friend this code for ms excel
and I want for code vb.net
thanks again

[Oralloy]

Hopefully I'm not wasting your time.

The logic is the same, the only differences are minor details (although I do recognize that the details can be very painful). The essential code loop should be as follows, although you will have to change the control name to whatever yours is:

  '' index variable
  Dim i As Long
  i = 0

  '' remove anything that looks like a blank
  While i < ListBox1.Items.Count
    If "" = Trim(ListBox1.Items(i).Text) Then
      ListBox1.Items.RemoveAt(i)
    Else
      i = 1 + i
    EndIf
  Wend

[Rabbit]

Shouldn't you just make sure no blank records are used for the list in the first place? Instead of removing the blanks after inserting them.

[Oralloy]

Rabbit,

That's probably the best approach; proper data selection beats data conditioning any day of the week.

I'm just hoping he hasn't bound the list to a source in such a way that he can't modify it in a reasonable fashion.

[cpanelxp]

thank you very muck mr.Oralloy
THAT'S IT
works successfully

[Oralloy]

Glad we could help you out.

Good Luck!
Oralloy