Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Remove empty items/lines in ListBox

0

Hi All

I'm new to this forum. I've been slowly learning Excel VBA for a few months and I need some help.

I just grabbed the following piece of code on the Internet in order to remove a blank row that keeps appearing at the bottom of a listbox that I created:

For i = ListBox1.Items.Count - 1 To 0 Step -1
    If String.IsNullOrEmpty(CStr(ListBox1.Items(i))) Then
        ListBox1.Items.RemoveAt(i)
    End If
Next

But it does not work. There seams to be some syntax error(not sure). 

I'm looking for something short and simple.

Any suggestion?

Thank you
Paul

[/CODE]

excelmacros-vba
MS Office Version: 365
posted by: Paul001 (rep: 6) on Tue Nov 1, 2022 at 11:08 pm
Answer
Discuss

Answers

0
Selected Answer

Hi Paul and welcome to the Forum

In the attached file, I've created a simple UserForm which is set up with a list of 7 items, 3 of which are blank or spaces- click the blue button on the worksheet to launch it. Click on the list and you'll see you can select 7 items.

Click on the green button on the form and the following, modified version of your code will run and reduce the list to the 4 values (code commented, with a revised If test and a key line in bold):

Private Sub CommandButton1_Click()

Dim i As Long

' loop backwards through items,
For i = ListBox1.ListCount - 1 To 0 Step -1
    ' check each if it contains meaningful text
    If Trim(ListBox1.List(i) & vbNullString) = vbNullString Then
        ' if not, delete that item
        ListBox1.RemoveItem (i)
    End If
Next i

End Sub

Hope you can use this approach for your project. If so, please remember to mark this Answer as Selected.

Also, good luck with your study of VBA.

posted by: John_Ru (rep: 6142) on Wed Nov 2, 2022 at 6:58 pm
Discuss

Discussion

Wow!!! It works flawlessly. 

Thank you so much for your quick response.

I wish you success and all the best.
Paul001 (rep: 6) Nov 3, '22 at 6:42 am
Glad it worked for you. Thanks for selecting my answer Paul. 
John_Ru (rep: 6142) Nov 3, '22 at 1:22 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login