Function to resize an array
Alan
Thanks alot...
I allready had a solution by mixing the 2 codes you gave
me, it works fine but yours has like 100 lines less so I
guess that in the long run is better...
Alos thanks again for the code and the lesson...
Cheers
Juan
-----Original Message-----
Juan Sanchez wrote:
Alan
Thanks alot!!!,
Just a few questions, just to see if i've got it
right...
1.- The key for shortening the code was the first
redim,
so that the FixArr Array could be indexed thus
eliminating
one loop, right?
I must confess that I didn't really get my head around
your code because
it wasn't easy for me to follow; I just wrote what I
thought was a
simpler way to do what you said you were trying to do.
2.- I noticed you changed my dims form Dim i,j,k as
Integer to Dim i as Integer, j as integer, the question
here is: is my way "illegal" on vba?
Not "illegal", but each variable must be separately typed.
Dim i, j, k As Integer declares i and j as Variant type
(the default
when no type is specified) and k as Integer. If you want
them all to be
Integer type you would use
Dim i As Integer, j As Integer, k As Integer
3.- What is the difference in redim when you use
Preserve??
Redim alone will wipe out and reinitialize all your array
elements;
ReDim Preserve retains the elements of the array.
I'm in the middle of cracking the vertical code, then I
will try to blend them together, if I have questions
I'll
post back.
I just had to change on your code the initialization of
k
was set to k=1, I changed to k=0, it was giving me one
more empty cell at the begining.
I'm afraid I'm not following this.
The following code will return a one-dimensional array of
the sought
values from either a two-row range or a two-column range:
Function CleanArr(OrArr As Variant) As Variant
Dim i As Long, j As Integer, k As Integer
Dim FixArr() As Variant, CPArr() As Variant
Dim numDimensions As Integer
On Error Resume Next
j = 1
Do
z = UBound(OrArr, j)
j = j + 1
Loop While Err = 0
Err = 0
numDimensions = j - 2
If numDimensions = 1 Then
CPArr = OrArr
Else
CPArr = Application.Transpose(OrArr)
End If
ReDim FixArr(UBound(CPArr) - LBound(CPArr) + 1)
k = LBound(CPArr)
For i = LBound(CPArr) To UBound(CPArr)
If CPArr(i) < "" Then
FixArr(k) = CPArr(i)
k = k + 1
End If
Next
ReDim Preserve FixArr(k - 1)
CleanArr = FixArr
End Function
Alan Beban
.
|