Reversing and trimming an array?

array(0) = "a"
array(1) = "b"
array(2) = "c"
array(3) = "d"

b’cos array start from 0(zero)

The array indexes starts on zero and not one. If you want to remove an index you need do create a new array and copy to there the values you want.

Why don-t you use collections? It's easier.

Dim coll As New Collection
coll.Add "a"
coll.Add "b"
coll.Add "c"

' removes the 3rd position
coll.Remove 3

dim i count as integer
dim a count as integer
array(0) = "a"
array(1) = "b"
array(2) = "c"
array(3) = "d"

i=4
a =0
while(i<=0)

 array(a)=array(i)

i=i-1
a=a+1
wend

dim i count as integer
dim a count as integer
array(0) = "a"
array(1) = "b"
array(2) = "c"
array(3) = "d"

i=4
a =0
while(i<=0)


 
array(a)=array(i)
if(i=3)then
exit loop
end if
i=i-1
a=a+1
wend

It's fine to start your array at 1, 2 or any other number for that matter. (In fact you can explicitly define the lower boundary as starting at 1… but I digress).

Generally arrays start at 0. E.g. For an array defined per "Dim vnArray(1)"
You have a one-dimensional array with 2 field (0 and 1)... and (in this example) it is a variant array. (Which means you can add anything to any field (including setting ‘fields’ to objects).

You can the expand only the last dimension of arrays of the fly but you can’t reduce it. As jpaulino has suggested you can use a collection instead of an array to achieve what you want… collections are like one-dimensional arrays… but you can add and remove from them.

In terms of finding out how big an array is, you use Ubound.
In the example array [created by Dim vnArray(1)]: Ubound(vnArray,1) would return 1 (i.e. the upper boundary of the first dimension).

Re your question though: If the right tool for the job is an array and you want to reduce it, you use a second (temporary array). I have provided two code examples of how you could do this.

Foot note: In both code examples my arrays have a lower boundary of zero, but I am ignoring it. Many people prefer to use arrays from the ‘field’ 1 up. Once you get used to using arrays though, field 0 it can be quite useful… but that is another story.

''' To remove the first field
Private Sub RemoveItem1(vnArray)

    Dim vnTempArray(), in1 As Integer

    ''' Dimension vnTempArray to one less than vnArray
        ReDim vnTempArray(UBound(vnArray) - 1)
    
    ''' Get all fields in vnArray, starting at field 2 and up to the upper bound of the array
        For in1 = 2 To UBound(vnArray, 1): vnTempArray(in1 - 1) = vnArray(in1)
        Next in1
    
    ''' Replace the original array (i.e. write the result back to your original array)
    ''' NB: As I am writing directly inro the variable of the calling procedure.
        vnArray = vnTempArray

End Sub

''' To remove all filed that equal 'a'
Sub RemoveAllAs(vnArray)

    Dim vnTempArray(), in1 As Integer

    ''' Make one dimensional Temp Array
        ReDim vnTempArray(0)
        
    ''' Transfer all fields in vnArray, except those with 'a' to the Temp Array
        For in1 = 1 To UBound(vnArray)
            If vnArray(in1) <> "a" Then
        ''' Expand the temp array (Redim Preserve allow syou to expand the last dimensionn of an array)
                ReDim Preserve vnTempArray(UBound(vnTempArray) + 1)
        ''' Transfer the qualifying field into the [new] upper boundary of the temp array
                vnTempArray(UBound(vnTempArray)) = vnArray(in1)
            End If
        Next in1
    
    ''' Replace the original array (i.e. write the result back to your original array)
    ''' NB: As I am writing directly inro the variable of the calling procedure.
         vnArray = vnTempArray

End Sub

Select allOpen in new window

The lower bound of the array depends on how it was declared and whether there is an Option Base statement in effect - they do not always start at 0.

To trim and flip, you'll need to loop backwards and populate, then redim but the exact code may depend on what you mean by "I'm not sure if the array goes above 3 though".

The array may only be 1 or 2 long, just so it doesnt bug up


The problem is i fill an array based on a pivot table, that lists by date. But I'm only interested in the latest 3, but I want them to show in reverse order. If the pivot is filtered to only show 1 date, the array will only contain 1 date

Why don't you fill from the bottom (or right) of the pivot table then, rather than trying to rejig it all afterwards?

Well, i get the length of the pivot by

intLength = (Worksheets("Data Availability").PivotTables("ava_1").TableRange2.Rows.Count)  - 5 

Select allOpen in new window

and then

For Each r In Worksheets("Data Availability").Range("$A$6:$C$" & (intLength + 5)).Rows
    varReliAxis(i) = r.Cells(1, 1).Value
Next r

Select allOpen in new window

If i filter it the other way, I do have it reversed, but I would then need to delete the first ones in the array?

Sorry, I don't follow you. You need to start with the bottom cell, then do the one above, then the one above that, then stop.

I agree with rorya: Probably easier to just get the data you want in the first place.
But, in the interest of staying with your question as posed… below is code to get “up to” the last three items from your array.

''' To just get up to the last three
Sub GetLastThree(vnArray)

    Dim vnTempArray(), in1 As Integer

    ''' Make one dimensional Temp Array
        ReDim vnTempArray(0)
        
    ''' Transfer top-most items from array (up to 3)
        Do
            ReDim Preserve vnTempArray(in1 + 1)
            vnTempArray(UBound(vnTempArray)) = vnArray(UBound(vnArray) - in1)
            in1 = in1 + 1
        Loop Until in1 = 3 Or UBound(vnArray) - in1 < 1
    
    ''' Replace the original array (i.e. write the result back to your original array)
    ''' NB: As I am writing directly inro the variable of the calling procedure.
         vnArray = vnTempArray

End Sub

Select allOpen in new window

So I can use that function like this?

myArray = GetLastThree(myArray)

And then myArray would now only contain the top 3?

Mostly right...
You can just run the sub from your main procedure as:
      GetLastThree myArray
The sub will write directly back to the callers array as passed (i.e. no need for MyArray =)
vnArray in the sub is ByRef, so vnArray is MyArray

Alternately, copy the code as provided into your main procedure and change vnArray to MyArray…
Both approaches will work

That works super!

Only problem is that I get a blank in the new array?

From A, B, C to
" ", C, B

Did i miss something?

Oh... (and forgive me if you are well aware of this):
If you copy the code into your main procedure and use an integer already defined (but not otherwise in use) ensure you set it to zero before the process.

I dont use any of those :/

Is there anyway to insert an if loop or similar that makes it skip the first line in the array to get around the blank?

yeah... didn't test it (sorry)...
Change:  Loop Until in1 = 3 Or UBound(vnArray) - in1 < 1
To:          Loop Until in1 = 2 Or UBound(vnArray) - in1 < 1

Thanks but that dindt fix it :/ The array still comes up with a blank as the first data point?

If you are staying up to date with my lats update... hold on a mo...

i changed the

ReDim Preserve vnTempArray(in1 + 1)

Select allOpen in new window

to

ReDim Preserve vnTempArray(in1)

Select allOpen in new window

And it works, I think, does that might cause problems later?

Just as an FYI, since you are doing this in Excel, you could just use application.min(2, ubound(vnarray)) to get the applicable size and declare your intermediate array as that size, rather than doing a Redim preserve in the loop.

The original code was correct... will exit the do loop when either the third element is retrieved or the next element in less than 1 in the array (i.e. in case your array has less than three elements)… but this assumes the array has an upper boundary equal to the number of elements (i.e. per your original “starting at 1”)

In the test you are running… does the array have more or less than 3 elements…?
And… are we still using an array where the upper boundary is the number of elements (or did you change to using ‘field’ 0…?

super! thanks a ton!