Delete Rows and Remove EmptyRow in Listbox

[sixman]

can anyone advise me of a good way to delete selected rows in my listbox that is populated from a spreadsheet. I have a "crude" delete button but I need it to delete the data and shift all those cells up. So far all it can do is delete the contents in the cells. The last problem I have is not so much a problem but more of advice on the best way to enter data in my spreadsheet with my textboxes. What I have now works fine, but I feel something behind the scenes is going awry. As my Listbox grows larger and larger the transaction time gets slower and slower. I have a feeling my coding is weak and could use a good clean-up

 

[BrianB]

It would be better to not add blanks in the first place. Here is some code which puts data from a worksheet to a 2 column listbox. It checks for blank values before adding. It is adapted from some existing code, so has not been tested.

If you really need to delete a single item, you just need to go from there to items.count minus 1 replacing each item with the one after it.

You might be better off using Data Validation.

    FileListBox.Clear
    Set DataSheet = ThisWorkbook.Worksheets("data")
    For Rw = 1 To 100
        If DataSheet.Cells(Rw, 1).Value <> "" Then
            FileListBox.AddItem
            FileListBox.List(Rw - 1, 0) = DataSheet.Cells(Rw, 1).Value
            FileListBox.List(Rw - 1, 1) = DataSheet.Cells(Rw, 2).Value
        End If
    Next