+ Reply to Thread
Results 1 to 15 of 15

Why are there Empty/Null/Blank Rows in my ListBox?

  1. #1
    Forum Contributor
    Join Date
    02-04-2014
    Location
    Florida, United States
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    366

    Why are there Empty/Null/Blank Rows in my ListBox?

    Hello,

    I have a UserForm with ListBox1 populated by Table1. The user can then use the TextBox1 to filter the ListBox1 selections.

    However, ListBox1 also contains blank/null/empty values that are NOWHERE to be found in my source Table1.

    What has happened here and how do I get rid of the empty rows?

    Thank You,

    Tyler B. Berger

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,674

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    It looks like this may be the culprit

    Please Login or Register  to view this content.
    You have almost 700 lines here in the range.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    It's because of this.
    Please Login or Register  to view this content.
    Pretty sure it should be Min instead of Max.
    If posting code please use code tags, see here.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,676

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    You would need to replace that line of code by something like the following ... in all the places you use it:

    Please Login or Register  to view this content.

    Regards, TMS

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    Alan

    The listbox isn't being populated from that range.

    Also there are no blank rows when one of the option buttons is selected.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,676

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    @Norie:

    on further investigation, I have to agree:

    Please Login or Register  to view this content.

    Though I think the 700 entry needs to be fixed too


    Regards, TMS

  7. #7
    Forum Contributor
    Join Date
    02-04-2014
    Location
    Florida, United States
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    366

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    First and foremost, thanks to all three of you. You have all responded to my threads. You have no idea how much value in financial terms and career growth you and other forums members provide to a novice such as myself.

    Secondly, Norie is correct.

    Third, TMS and Norie may have a solution, I will let you know.
    Last edited by EnigmaMatter; 03-02-2014 at 08:29 PM.

  8. #8
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,674

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    Norie and Trevor. Thanks for the info.

    Alan

  9. #9
    Forum Contributor
    Join Date
    02-04-2014
    Location
    Florida, United States
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    366

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    Norie,

    You solved it!

    Why did that work?

  10. #10
    Forum Contributor
    Join Date
    02-04-2014
    Location
    Florida, United States
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    366

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    Is it acceptable to simply give Rep or should I leave comments too?

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,676

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    Is it acceptable to simply give Rep or should I leave comments too?
    It's fine either way. It's kind of you to take the trouble; if you want to add something, that's great but you've pretty well got it covered.

    Thanks for the rep

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,676

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    If you're taking the MAX, then 65536 is likely to be the MAX. As Norie points out, it you need the MIN. Being pedantic, you should probably use Rows.Count rather than 65536

  13. #13
    Forum Contributor
    Join Date
    02-04-2014
    Location
    Florida, United States
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    366

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    How would I go about changing the OptionButton code so that it doesnt refer to A10:A700, but to Table1?

    In other words, how do I fix that as you say?

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    You could use the table like this.
    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    02-04-2014
    Location
    Florida, United States
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    366

    Re: Why are there Empty/Null/Blank Rows in my ListBox?

    Thanks! I appreciate that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Insert empty/blank rows
    By Danielle22 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2013, 04:30 PM
  2. Fill a LISTBOX from a range, WHITOUT BLANK ROWS
    By aleebaba in forum Excel - New Users/Basics
    Replies: 11
    Last Post: 11-07-2011, 03:06 AM
  3. Problem with empty rows appearing in listbox
    By Julesdude in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-30-2010, 06:32 AM
  4. Find empty/null/blank cell in a conditional format
    By Dabooj in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-09-2008, 10:50 AM
  5. How to delete rows that include blank/null cells
    By kostas in forum Excel General
    Replies: 5
    Last Post: 04-27-2007, 07:47 AM

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