ComboBox List from Range that selects a range until last row
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: ComboBox List from Range that selects a range until last row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2016
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default ComboBox List from Range that selects a range until last row

    So I have a userform that initializes with the following code. The comboboxes are named CB_AQUEOUS and CB_SOLDIS. I have ranges in Sheet1 that I source the lists from. But if the ranges change, I have to manually update the code. Is there a way I can simply do a *last row* on this? Is the fact that this is in an initialization routine problematic?

    Code:
    Private Sub UserForm_Initialize()
    
    Me.DTPicker1 = Date - 190
    Me.DTPicker2 = Date
    
    'This is the code I want to look at.
    CB_AQUEOUS.List = Worksheets("Sheet1").Range("A2:A36").Value
    CB_SOLIDS.List = Worksheets("Sheet1").Range("B2:B11").Value
    CB_AQUEOUS.Value = "---Please Select---"
    CB_SOLIDS.Value = "---Please Select---"
    
    
    Set mwksWorksheet = wWorksheetPVar
    msICP = sSamplePVar
    msICP = sICPPVar & sSamplePVar
    Call PopulateControlArrays
    Call MonthButtonColor
    Call SetSummary
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,615
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: ComboBox List from Range that selects a range until last row

    How about
    Code:
    With Worksheets("Sheet1")
       CB_AQUEOUS.List = .Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
       CB_SOLIDS.List = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value
    End With
    CB_AQUEOUS.Value = "---Please Select---"
    CB_SOLIDS.Value = "---Please Select---"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Sep 2016
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ComboBox List from Range that selects a range until last row

    Good day, tried that code and I'm getting a "Run-time error '1004' Application-defined or object-defined error"

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,615
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: ComboBox List from Range that selects a range until last row

    Which line of code returned the error?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,467
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    14 Thread(s)

    Default Re: ComboBox List from Range that selects a range until last row

    Perhaps
    Code:
    Private Sub UserForm_Initialize()
    
    Me.DTPicker1 = Date - 190
    Me.DTPicker2 = Date
    
    'This is the code I want to look at.
    With Worksheets("Sheet1")
        With Range("A:A")
            CB_AQUEOUS.List = Range((.Cells(2,1), .Cells(Rows.Count,1).End(xlup)).Value
        End With
        With Range("B:B")
            CB_SOLIDS.List = Range((.Cells(2,1), .Cells(Rows.Count,1).End(xlup)).Value
        End With
    End With
    
    '...
    End Sub

  6. #6
    Board Regular
    Join Date
    Sep 2016
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ComboBox List from Range that selects a range until last row

    @Fluff
    The code is failing at
    Code:
    CB_AQUEOUS.List = .Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    @Mike
    Your code runs but I had to remove the first bracket (code font was red indicating syntax error). But it seems it's grabbing the range not from "Sheet" one but from another sheet where the userform is launched.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,615
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: ComboBox List from Range that selects a range until last row

    Oops, I missed a . before the 2nd Range. It should be
    Code:
    CB_AQUEOUS.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
    Last edited by Fluff; Jul 18th, 2019 at 11:09 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Sep 2016
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ComboBox List from Range that selects a range until last row

    Yes, that worked... @Mike - curious as to why your code is pulling from another Worksheet.
    @Fluff - How does the rows.count know which column it's dealing with (in my sheet column A and B have different counts)

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,615
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: ComboBox List from Range that selects a range until last row

    Rows.Count, simply counts the number of available rows in the sheet. So these days that's 1048576, but when using an xls file it's only 65536

    Mike made the same mistake as me
    Code:
    With Worksheets("Sheet1")
        With .Range("A:A")
            CB_AQUEOUS.List = Range(.Cells(2,1), .Cells(Rows.Count,1).End(xlup)).Value
        End With
        With .Range("B:B")
            CB_SOLIDS.List = Range(.Cells(2,1), .Cells(Rows.Count,1).End(xlup)).Value
        End With
    End With
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Sep 2016
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ComboBox List from Range that selects a range until last row

    Right, that clears up some confusion - I was thinking 'wouldn't it be xldown?' Anyhow, good lesson here. Cheers!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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