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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,969
Messages
5,856,590
Members
431,823
Latest member
irtezazaz

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