Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    custom lists - non-contiguous (97-2007)

    I have tried this in 97, 2000, 2003 & 2007Trial and seek confirmation that it can't be done. Or at least, that I'm not missing a vital step.
    I am familiar with creating/importing custom lists, but tried selecting two non-contiguous blocks of cells; I want to assemble a single custom list from two separate blocks of cells in the same worksheet.
    Excel treats each block as a separate list, although the "Import" range box indicates that the two blocks of cells have been recognised.
    Attached Images Attached Images

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

    Re: custom lists - non-contiguous (97-2007)

    As you note, Excel will treat each contiguous area as a separate range.
    You can copy / paste the individual areas until you have a contiguous range, then import it.

  3. #3
    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: custom lists - non-contiguous (97-2007)

    The custom lists are not "links" they are imports: you are importing them in and the cells themselves do not matter.

    By choosing non-contiguous list, you are telling excel to import multiple lists.

    All you have to do is to create a contiguous list in one group of cells, import and then the 2 lists can be separated again.

    Steve

  4. #4
    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: custom lists - non-contiguous (97-2007)

    In addition to just creating a contiguous import range, you could use a macro to create the list. This reads the cells in the areas into an array and then adds it to the custom list.

    <pre>Option Explicit
    Sub CreateCustomList()
    Dim iCount As Integer
    Dim rArea As Range
    Dim rCell As Range
    Dim vArray() As String
    iCount = 0
    For Each rArea In Selection.Areas
    For Each rCell In rArea
    ReDim Preserve vArray(iCount)
    vArray(iCount) = rCell.Value
    iCount = iCount + 1
    Next
    Next
    Application.AddCustomList _
    ListArray:=vArray
    Set rArea = Nothing
    Set rCell = Nothing
    End Sub</pre>


    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: custom lists - non-contiguous (97-2007)

    > you are telling excel to import multiple lists

    Right, but the dialogue box shows a set of ranges, giving the impression that it is "a list from two sets of cells".
    Thanks for the VBA.

  6. #6
    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: custom lists - non-contiguous (97-2007)

    The dialogue shows you just what you have selected on the sheet which can be imported. It has nothing to do with what you have selected as the list. It is just the items that will be imported when you press import.

    The items in the list that is selected are given in the listbox.

    The top half of the dialogue is distinct fromt he bottom half.

    The import allows you to import multiple lists in one operation, by selecting multiple ranges. This saves time instead of creating each list individually.

    Steve

Posting Permissions

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