+ Reply to Thread
Results 1 to 4 of 4

ComboBox ListFillRange without the blank rows in the range

  1. #1
    Registered User
    Join Date
    01-14-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    10

    ComboBox ListFillRange without the blank rows in the range

    Hi,

    I have an excel spreadsheet which has two activeX ComboBox's on it. The selection of the 1st ComboBox determines the ListFillRange of the second ComboBox by referencing named ranges called "Yes" and "No", when "Yes" is selected the 2nd ComboBox changes it's contents to the named range "YesServicePlus" and when "No" is selected it fills the contents with a named range called "NoServicePlus".

    The problem is, my named ranges are entire columns, as the contents of these ranges are updated regularly by inexperienced excel operators (who I don't want redefining new ranges).This results in a lot of whitespace/blank rows at the end of the combobox list. Is there a way to removed this and only insert a ListFillRange with rows that actually have a value in them? meaning I don't have to specify the exact rows with data in them as my named range.

    My VBA coce to fill the 2nd ComboBox is:


    Code:
    XtracareComboBox.ListFillRange = ComboBox1.Value
    Any help appreciated.

    Dan

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: ComboBox ListFillRange without the blank rows in the range

    You could try creating the first range (list) as a Dynamic Named Range by following the steps outlined here:
    http://www.excel-easy.com/examples/d...med-range.html
    - Moo

  3. #3
    Registered User
    Join Date
    01-14-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    10

    Re: ComboBox ListFillRange without the blank rows in the range

    Thanks for that. Dynamic named range fixed the problem. Thanks.

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: ComboBox ListFillRange without the blank rows in the range

    If that takes care of your question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

    - Moo

+ 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. Combobox ListFillRange using dynamic named range not updating
    By anteagles20 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-21-2013, 09:30 AM
  2. [SOLVED] Combobox ListFillRange Using Formula Named Range
    By anteagles20 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2013, 07:40 AM
  3. ComboBox Listfillrange
    By haddock in forum Excel General
    Replies: 2
    Last Post: 01-26-2009, 05:44 PM
  4. Combobox ListFillRange
    By jwiggers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-25-2007, 12:54 AM
  5. Transpose Combobox ListFillRange
    By crazybass2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2005, 08:06 PM
  6. ComboBox here ... ListFillRange there
    By Kent in forum Excel General
    Replies: 0
    Last Post: 04-05-2005, 12:33 PM
  7. ListfillRange for ComboBox
    By oldbyte in forum Excel General
    Replies: 0
    Last Post: 02-23-2005, 05:35 PM
  8. [SOLVED] How to Set VB ComboBox ListFillRange to a Defined Range in an external workbook
    By Ken in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2005, 01:06 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