Remove blank rows from list box

**delolding** · 08-29-2012, 11:36 AM

Trying to remove blank lines from listbox
Trying to use this code for excel 2003
but am getting run time error 438 on 4th line
object does not support this property or method

Please Login or Register  to view this content.

**rvasquez** · 08-29-2012, 11:44 AM

Hello there,

Have you tried replacing your existing listbox with an ActiveX control Listbox instead. It is easier to perform the action you are trying to execute.

If you do that then your code would look like this:

Please Login or Register  to view this content.

**delolding** · 08-29-2012, 11:51 AM

does 2003 have acxtive X

**rvasquez** · 08-29-2012, 11:58 AM

I believe so. Try creating it by using this line of code

Please Login or Register  to view this content.

This should add the listbox to the first worksheet in your workbook.

**arlu1201** · 08-29-2012, 12:00 PM

Delolding,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.

**delolding** · 08-29-2012, 12:10 PM

My version is without active x

**rvasquez** · 08-30-2012, 12:19 PM

Hello there,

I couldn't figure out how to add items to a non ActiveX control list box but I found a work around:

Please Login or Register  to view this content.

Basically the code filters column A to column L and filters only unique values. Where column A is the original data and column L will not be the unique values from column A. Then it sort the values in column L ascending so that the blank cell does on the bottom. Finally sets the listbox's listitems to column L from row 1 to the last row that contains a value.