Remove blank items from a listbox

[gaudrco]

I have a listbox that loads the list items from a range of cells. Some of the cell values in the range are blank, therefore when the code is executed, the listbox loads the blank cells in as well. I would like to see if there is a way to have the code remove the blank values from the list. I've researched this on google but haven't been able to find a way to get it to work for my code. Here is my current code:

Private Sub UserForm_Activate()
Listboxreorder.Clear
For i = 4 To 40
    If Columns(i).Hidden = False Then Listboxreorder.AddItem Cells(7, i).Value
Next
End Sub
Sub UpDate_List()
Listboxreorder.Clear


 With Listboxreorder.List
    With SpinButton1
        .Value = 0
        .Max = 1
        .Min = -1
        .SmallChange = 1
    End With
    End With
    End Sub


    Private Sub SpinButton1_SpinDown()
    MoveItem 1
End Sub


Private Sub SpinButton1_SpinUp()
    MoveItem -1
End Sub


Private Sub MoveItem(X As Long)
    Dim itms As String, i As Long, lbVal As String, newPos As Long
'move the item
    With Me.Listboxreorder
        If .ListIndex > -1 Then
            newPos = .ListIndex + X
            If newPos < 0 Or newPos = .ListCount Then Exit Sub
            lbVal = .Value
            itms = .List(newPos)
            .List(newPos) = .List(.ListIndex)
            .List(.ListIndex) = itms
        End If
're-select original item
        For i = 0 To .ListCount - 1
            If .List(i) = lbVal Then .Selected(i) = True
        Next i
     End With
End Sub

 

[Paul Ked]

Why load all of the cells and not just the ones that contain a value?

    If Columns(i).Hidden = False [COLOR=#ff0000]And Cells(7, i) <> ""[/COLOR] Then Listboxreorder.AddItem Cells(7, i).Value

 

[gaudrco]

Great idea this was perfect

 

[Paul Ked]

You're welcome