Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Set Range (2003sp2)

    I am attempting to populate a list box. Here is what I have so far:
    <pre>Private Sub cmdJobList_Click()
    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim Item

    Set AllCells = Range(Worksheets(2).Range("A2"), Worksheets(2).Range("A2").End(xlDown))

    On Error Resume Next
    For Each Cell In AllCells
    NoDupes.Add Cell.Value, CStr(Cell.Value)
    Next Cell
    On Error GoTo 0
    For Each Item In NoDupes
    UserForm1.lbJobs.AddItem Item
    Next Item
    UserForm1.Show
    End Sub</pre>


    This button is located on Sheet1. The list I need to use is on Sheet2 and starts in cell A2. This range will change, so I was hoping to use End(xlDown) to get the second part of my range as you can see in the line "Set AllCells". What am I missing on this?

    thanks,
    jackal

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Range (2003sp2)

    If the 2nd worksheet is not the active sheet, you must use

    Set AllCells = Worksheets(2).Range(Worksheets(2).Range("A2"), Worksheets(2).Range("A2").End(xlDown))

    Otherwise, AllCells will be a range on the active sheet. But why not set the RowSource of the list box directly to the address of the range?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set Range (2003sp2)

    My Range that starts in Sheet2!A2, contains duplicates and I don't want the duplicates in the list. And this list is populated from a Query and will vary in size. I hope this answer your question?

    thanks,
    jackal

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Range (2003sp2)

    OK. Did my previous reply help?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set Range (2003sp2)

    Yes, it did help. Why could it not be written as follows:

    Set AllCells = Worksheets(2).Range(Range("A2"), Range("A2").End(xlDown))

    Just curious??? Isn't the "Worksheets(2) at the beginning telling what sheet to look at?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Range (2003sp2)

    Each time you use Range without prefixing it with a sheet object, it is assumed to be on the active sheet. So you *must* repeat Worksheets(2). for each Range.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Set Range (2003sp2)

    TO avoid listing it 3 times you can use a WITH construction

    With Worksheets(2)
    Set AllCells = .Range(.Range("A2"), .Range("A2").End(xlDown))
    end with

    (note the period(.) before each range explicitly linking it to the "With object")

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set Range (2003sp2)

    Thanks U 2 for the info.

    thanks,
    jackal

Posting Permissions

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