Delete Rows and Remove EmptyRow in Listbox

sixman

New Member
Joined
Jan 11, 2004
Messages
11
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.
Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top