Blank Rows At The Bottom Of A UserForm Listbox When Duplicates Are Deleted

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
I have a several listboxes, one on each page on multipage. The first listbox shows the data that has gone into sheet2. After the code is completed it deletes any duplicate records in column A of sheet 2. Therefore originally the listbox may have been populated with 100 records, 25 of which were duplicates, these are deleted at the end, leaving 75 unique records. The listbox ends up with blank rows at the bottom.

How can I prevent the blank rows from showing

Userform1
MultiPage1
Listbox1
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
Its Ok I have fix the problem, I had this in my code to track the data as it went into my list box, this created a scroll affect,

VBA Code:
With UserForm1.ListBox1
         UserForm1.ListBox1.ColumnCount = 1
         UserForm1.ListBox1.ColumnWidths = "600"
         UserForm1.ListBox1.RowSource = "'" & Sheet2.Name & "'!$A$1:$b$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).row
        UserForm1.ListBox1.ListIndex = ExcelWebScraper.UrlsListBox1.ListCount - 1 'Allow Listbox To SCROLL as data goes in
    End With

After the code had finished, and duplicates were deleted I just re-added this
VBA Code:
With UserForm1.ListBox1
         UserForm1.ListBox1.ColumnCount = 1
         UserForm1.ListBox1.ColumnWidths = "600"
         UserForm1.ListBox1.RowSource = "'" & Sheet2.Name & "'!$A$1:$b$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).row
    End With

It hide all Blank rows. Only rows with data in them now show in the list box.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,583
Messages
5,625,622
Members
416,124
Latest member
DeMoNloK

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