+ Reply to Thread
Results 1 to 6 of 6

list box-hide empty rows

  1. #1
    Registered User
    Join Date
    10-15-2008
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    93

    list box-hide empty rows

    Is it possible to have a list box only list rows that contain data in them?
    For example if you have a list box whose input field is A1:A10. If rows A2:A9 are empty, I would like the first (only) two rows in the list box to be A1 and A10.

    I am using a Form control not an Active X control list box, because the Active x one was giving me other problems.

    I tried inserting a macro that actually hides the empty rows (A2:A9 in this example) on the actual sheet, but that doesn't make any difference to what appears in the list box - you still see all the empty rows there.

    Thanks
    Sunil

  2. #2
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,651

    Re: list box-hide empty rows

    Try This:-
    Please Login or Register  to view this content.
    Regards Mick

  3. #3
    Registered User
    Join Date
    10-15-2008
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: list box-hide empty rows

    Hi Mick

    Thanks for your suggestion.

    My example of rows A1 and A10 being the ones with data is just one possible scenario. This is actually driven by the user. I may be that rows A1, A2 and A10 have data with the rest in the middle being blank.

    Can you suggest an alternative code which will delist the blank rows whatever they may be?

    Thanks
    Sunil

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,651

    Re: list box-hide empty rows

    Hi, This will fill your ListBox with all cell values in column "A" that are not Blank.
    Please Login or Register  to view this content.
    Regards Mick

  5. #5
    Registered User
    Join Date
    08-29-2012
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: list box-hide empty rows

    Hi tried for Excel 2003 but says
    run time error 438
    object doesnt support this propoerty or method

  6. #6
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: list box-hide empty rows

    Delolding,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1