+ Reply to Thread
Results 1 to 5 of 5

excel vba - listbox displays first row when empty

  1. #1
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    excel vba - listbox displays first row when empty

    It bugs me that the first row shows up if the listbox is empty on form initialization. Properties/ColumnHeads = false. If I use a table, the headers don't show up, but I'm not finding a lot of information on saving an entire listbox to a table online (for excel anyways - although is someone can direct me to that, I would be most obliged). I would have thought that by declaring the rng start at A2, that the listbox would not show what's in the Sheets first row. Can someone help me understand why this is?
    Please Login or Register  to view this content.

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

    Re: excel vba - listbox displays first row when empty

    If column A is empty then LastRow will return 1 so rng will refer to A2:C1, or more conventionally A1:C2.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: excel vba - listbox displays first row when empty

    Thank you. That makes sense. This may not be an educated question, so pardon me if this seems dense, but if a range is empty (except column headers), do listboxes see the LastRow then as an index of 1, showing the LastRow (A1) until something is added to A, in which (A2) then becomes the LastRow?

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

    Re: excel vba - listbox displays first row when empty

    Sorry I don't follow.

    When you populate a listbox from a range using List there's no distinction between headers and data, everything is just seen as data.

    So if the range was empty and you populated the listbox with A1:C2 then the 'header' row, i.e. the row in the listbox showing the headers, would have index 0 and the empty row below that would have index 1.

  5. #5
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: excel vba - listbox displays first row when empty

    Apologies, but that is the answer I was after. Thanks.

+ 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. Excel Userform Search Command- Listbox displays results
    By KathyES93 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-07-2017, 02:42 AM
  2. [SOLVED] If ListBox empty VBA not working
    By sintek in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 10-14-2016, 06:30 AM
  3. [SOLVED] Multi-select ListBox Displays Sheets Then Runs Code
    By Shea2288 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-04-2016, 03:50 PM
  4. [SOLVED] if anything in listbox select first item, if listbox empty do nothing (listbox in userform
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-17-2015, 12:49 PM
  5. how to update listbox list
    By jgcramer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-16-2013, 04:19 AM
  6. Replies: 2
    Last Post: 11-02-2011, 10:55 AM
  7. Can't get listbox which displays rows in sheet to add texbox value to selected row
    By Julesdude in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 07-02-2010, 01:43 PM

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