VBA: How to clear listbox items?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: VBA: How to clear listbox items?

  1. #1
    Board Regular dbmathis's Avatar
    Join Date
    Sep 2002
    Location
    Austin, Texas USA
    Posts
    1,064
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: How to clear listbox items?

     
    Hi all,

    I have a simple question. How does one clear a listbox with VBA?

    Thanks
    After all this is over, all that will really have mattered is how we treated each other.

    Excel Version: 2007
    OS: Windows 7

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    72,969
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do you mean by clear?

    Clear the contents?
    Code:
    Listbox1.Clear
    Clear the selection(s)?
    Code:
    Listbox1.ListIndex = -1
    If posting code please use code tags.

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If it's filled with AddItem:

    ListBox1.Clear

    If it's filled with RowSource:

    ListBox1.RowSource = ""

  4. #4
    Board Regular dbmathis's Avatar
    Join Date
    Sep 2002
    Location
    Austin, Texas USA
    Posts
    1,064
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hmmm. It was actually filled with an array. I tried .clear before I posted and it didn't work? Now it does? Must have been a typo.

    Thanks for the prompt response.
    After all this is over, all that will really have mattered is how we treated each other.

    Excel Version: 2007
    OS: Windows 7

  5. #5
    New Member
    Join Date
    Dec 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: How to clear listbox items?

    If you use the .clear method it leaves empty rows at the end of the list. If i fill it up with a smaller list than it will leave empty entries that i can select at the bottom of the list. Does it function like an array and i need to redim it?

  6. #6
    New Member
    Join Date
    Mar 2015
    Location
    Georgia, USA
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: How to clear listbox items?

    Important response on different method to clear based on how it was loaded. I was getting an "unspecified error" when using ListBox1.Clear because it was filled with RowSource. Changed to ListBox1.RowSource = "" and it works great. Thanks

  7. #7
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,877
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: How to clear listbox items?

      
    Doing what Mustang0710 did in post #6 is what I needed, however, is there a way to retain the header rows in the listbox after the other rows have been cleared?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com