+ Reply to Thread
Results 1 to 15 of 15

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

  1. 03-02-2014, 07:31 PM #1
    EnigmaMatter
    EnigmaMatter is offline
    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
    Reply With QuoteRegister To Reply
  2. 03-02-2014, 07:55 PM #2
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,870

    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
    Reply With QuoteRegister To Reply
  3. 03-02-2014, 07:56 PM #3
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    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.
    Reply With QuoteRegister To Reply
  4. 03-02-2014, 08:06 PM #4
    TMS
    TMS is offline
    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
    44,409

    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
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  5. 03-02-2014, 08:11 PM #5
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    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.
    Reply With QuoteRegister To Reply
  6. 03-02-2014, 08:26 PM #6
    TMS
    TMS is offline
    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
    44,409

    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
    Reply With QuoteRegister To Reply
  7. 03-02-2014, 08:27 PM #7
    EnigmaMatter
    EnigmaMatter is offline
    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.
    Reply With QuoteRegister To Reply
  8. 03-02-2014, 08:30 PM #8
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,870

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

    Norie and Trevor. Thanks for the info.

    Alan
    Reply With QuoteRegister To Reply
  9. 03-02-2014, 08:33 PM #9
    EnigmaMatter
    EnigmaMatter is offline
    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?
    Reply With QuoteRegister To Reply
  10. 03-02-2014, 08:35 PM #10
    EnigmaMatter
    EnigmaMatter is offline
    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?
    Reply With QuoteRegister To Reply
  11. 03-02-2014, 08:46 PM #11
    TMS
    TMS is offline
    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
    44,409

    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
    Reply With QuoteRegister To Reply
  12. 03-02-2014, 08:48 PM #12
    TMS
    TMS is offline
    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
    44,409

    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
    Reply With QuoteRegister To Reply
  13. 03-02-2014, 09:02 PM #13
    EnigmaMatter
    EnigmaMatter is offline
    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?
    Reply With QuoteRegister To Reply
  14. 03-02-2014, 09:13 PM #14
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    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.
    Reply With QuoteRegister To Reply
  15. 03-02-2014, 09:25 PM #15
    EnigmaMatter
    EnigmaMatter is offline
    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1