# Reversing and trimming an array?

Hello EE!

I've run into some problems with arrays. I find arrays to be pretty hard to understand, but I've managed to make an array like so:

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

Is there any way, to easily flip the array and trim it to 3 max (I'm not sure if the array goes above 3 though)?

So the array becomes:
array(1) = "d"
array(2) = "c"
array(3) = "b"

Hope you can help!
Microsoft Excel

Last Comment
ThomasFoege

8/22/2022 - Mon
ukerandi

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

b’cos array start from 0(zero)
Jorge Paulino

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

' removes the 3rd position
coll.Remove 3
ukerandi

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ukerandi

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
NeillZ

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
``````
Rory Archibald

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".
Get an unlimited membership to EE for less than \$4 a week.
Unlimited question asking, solutions, articles and more.
ThomasFoege

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
Rory Archibald

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

Well, i get the length of the pivot by

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

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
``````

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?

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rory Archibald

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.
NeillZ

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
``````
ThomasFoege

So I can use that function like this?

myArray = GetLastThree(myArray)

And then myArray would now only contain the top 3?
Get an unlimited membership to EE for less than \$4 a week.
Unlimited question asking, solutions, articles and more.
NeillZ

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
ThomasFoege

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?
NeillZ

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ThomasFoege

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?
NeillZ

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
ThomasFoege

Thanks but that dindt fix it :/ The array still comes up with a blank as the first data point?
Get an unlimited membership to EE for less than \$4 a week.
Unlimited question asking, solutions, articles and more.
NeillZ

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

i changed the

``````ReDim Preserve vnTempArray(in1 + 1)
``````
to

``````ReDim Preserve vnTempArray(in1)
``````

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

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
NeillZ

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…?
NeillZ