+ Reply to Thread
Results 1 to 3 of 3

Resize array based on number of 'used' elements

  1. #1
    John
    Guest

    Resize array based on number of 'used' elements

    Hi there,

    So here's my second question of the afternoon..................

    I'm creating an array of unique words based on another list passed to the
    procedure (see code below). This works ok but I'd like to resize the
    resulting array (vUniqueList) to be the same number as the elements that are
    not empty. So can anyone tell me if there's a way of doing this without
    iterating through the array to test for empty elements?

    Best regards

    John



    '--------------------------
    'Sample passed as parameter
    Dim sWordLists As String
    sWordLists = "Carrot%Carrot%Rabbit"
    '--------------------------

    Dim itm As Variant
    Dim vUniqueList As Variant

    ReDim vUniqueList(UBound(Split(sWordLists, "%"))) As Variant

    'Get unique list
    For Each itm In Split(sWordLists, "%")
    For i = 0 To UBound(vUniqueList)
    If IsEmpty(vUniqueList(i)) = True Then
    vUniqueList(i) = itm
    Exit For
    Else
    If itm = vUniqueList(i) Then
    Exit For
    End If
    End If
    Next i
    Next itm



  2. #2
    Dave Peterson
    Guest

    Re: Resize array based on number of 'used' elements

    One way:

    Option Explicit
    Sub testme()

    'Sample passed as parameter
    Dim sWordLists As String
    Dim iCtr As Long
    Dim wCtr As Long
    Dim vUniqueList As Variant
    Dim mySplit As Variant
    Dim res As Variant

    sWordLists = "Carrot%Carrot%Rabbit%3"
    mySplit = Split(sWordLists, "%")

    ReDim vUniqueList(LBound(mySplit) To UBound(mySplit))

    wCtr = LBound(mySplit) - 1

    For iCtr = LBound(mySplit) To UBound(mySplit)
    res = Application.Match(mySplit(iCtr), vUniqueList, 0)
    If IsError(res) Then
    'not yet in vuniquelist
    wCtr = wCtr + 1
    vUniqueList(wCtr) = mySplit(iCtr)
    End If
    Next iCtr

    ReDim Preserve vUniqueList(LBound(mySplit) To wCtr)

    End Sub

    You want another way?

    John Walkenbach shows how to use a collection to get a unique list. He also
    shows how that list can be sorted (you may want to do that):
    http://j-walk.com/ss/excel/tips/tip47.htm



    John wrote:
    >
    > Hi there,
    >
    > So here's my second question of the afternoon..................
    >
    > I'm creating an array of unique words based on another list passed to the
    > procedure (see code below). This works ok but I'd like to resize the
    > resulting array (vUniqueList) to be the same number as the elements that are
    > not empty. So can anyone tell me if there's a way of doing this without
    > iterating through the array to test for empty elements?
    >
    > Best regards
    >
    > John
    >
    > '--------------------------
    > 'Sample passed as parameter
    > Dim sWordLists As String
    > sWordLists = "Carrot%Carrot%Rabbit"
    > '--------------------------
    >
    > Dim itm As Variant
    > Dim vUniqueList As Variant
    >
    > ReDim vUniqueList(UBound(Split(sWordLists, "%"))) As Variant
    >
    > 'Get unique list
    > For Each itm In Split(sWordLists, "%")
    > For i = 0 To UBound(vUniqueList)
    > If IsEmpty(vUniqueList(i)) = True Then
    > vUniqueList(i) = itm
    > Exit For
    > Else
    > If itm = vUniqueList(i) Then
    > Exit For
    > End If
    > End If
    > Next i
    > Next itm


    --

    Dave Peterson

  3. #3
    John
    Guest

    Re: Resize array based on number of 'used' elements

    Thanks Dave. Makes perfect sense and the use of Match seems a tidier
    method.

    Thanks for your help.

    Best regards

    John


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > One way:
    >
    > Option Explicit
    > Sub testme()
    >
    > 'Sample passed as parameter
    > Dim sWordLists As String
    > Dim iCtr As Long
    > Dim wCtr As Long
    > Dim vUniqueList As Variant
    > Dim mySplit As Variant
    > Dim res As Variant
    >
    > sWordLists = "Carrot%Carrot%Rabbit%3"
    > mySplit = Split(sWordLists, "%")
    >
    > ReDim vUniqueList(LBound(mySplit) To UBound(mySplit))
    >
    > wCtr = LBound(mySplit) - 1
    >
    > For iCtr = LBound(mySplit) To UBound(mySplit)
    > res = Application.Match(mySplit(iCtr), vUniqueList, 0)
    > If IsError(res) Then
    > 'not yet in vuniquelist
    > wCtr = wCtr + 1
    > vUniqueList(wCtr) = mySplit(iCtr)
    > End If
    > Next iCtr
    >
    > ReDim Preserve vUniqueList(LBound(mySplit) To wCtr)
    >
    > End Sub
    >
    > You want another way?
    >
    > John Walkenbach shows how to use a collection to get a unique list. He
    > also
    > shows how that list can be sorted (you may want to do that):
    > http://j-walk.com/ss/excel/tips/tip47.htm
    >
    >
    >
    > John wrote:
    >>
    >> Hi there,
    >>
    >> So here's my second question of the afternoon..................
    >>
    >> I'm creating an array of unique words based on another list passed to the
    >> procedure (see code below). This works ok but I'd like to resize the
    >> resulting array (vUniqueList) to be the same number as the elements that
    >> are
    >> not empty. So can anyone tell me if there's a way of doing this without
    >> iterating through the array to test for empty elements?
    >>
    >> Best regards
    >>
    >> John
    >>
    >> '--------------------------
    >> 'Sample passed as parameter
    >> Dim sWordLists As String
    >> sWordLists = "Carrot%Carrot%Rabbit"
    >> '--------------------------
    >>
    >> Dim itm As Variant
    >> Dim vUniqueList As Variant
    >>
    >> ReDim vUniqueList(UBound(Split(sWordLists, "%"))) As Variant
    >>
    >> 'Get unique list
    >> For Each itm In Split(sWordLists, "%")
    >> For i = 0 To UBound(vUniqueList)
    >> If IsEmpty(vUniqueList(i)) = True Then
    >> vUniqueList(i) = itm
    >> Exit For
    >> Else
    >> If itm = vUniqueList(i) Then
    >> Exit For
    >> End If
    >> End If
    >> Next i
    >> Next itm

    >
    > --
    >
    > Dave Peterson




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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