Hi all...
I was trying to write a function that would help me resize
an array in such a way that when I apply the function to
it, it will eliminate the *empty spaces* of the array.

The array I'm trying to modifie is like the one that would
return a formula like =IF(A1:J1=1,A2:J2,"") for each cell
different from 1 on row 1 I would get a empty space in my
array so if I have

A B C D E F G H I J
R1 1 2 1 1 3 2 1 2 2 1
R2 3 4 2 5 7 2 6 4 5 8

my formula would result in {3, ,2,5, , ,6, , ,8}
and after applying my new function which I called
CleanArr, the result would be {3,2,5,6,8}

Here's were I'm at now:
'========================================
Function CleanArr(OrArr As Variant) As Variant
Dim i, j, k As Integer
Dim FixArr(), CPArr() As Variant

CPArr = OrArr
i = 0
For j = 1 To UBound(CPArr())
If CPArr(j) < Empty Then i = i + 1
Next j
ReDim FixArr(i - 1)
j = 1
For k = 0 To UBound(FixArr())
If CPArr(j) < Empty Then
FixArr(k) = CPArr(j)
Else
k = k - 1
End If
j = j + 1
Next k
CleanArr = FixArr
End Function
'========================================

As it is above, it works, if I use:
=CleanArr(IF(A1:J1=1,A2:J2,"")CTR+SHF+ENT, it will return
{3,2,5,6,8}

But... and here's were I'm stuck, if I use it in a column
orientation i.e. =CleanArr(IF(A1:A10=1,B1:B10,""))

It returns #VALUE!

I am LOST how can I make it work in both orientations,
it's intended for single dimension arrays but I want it to
work either in a row or a column...

Any help is greatly appreciated...

TIA
Juan

First, I would simplify the horizontal one-row code, a la something like

Function CleanArrH(OrArr As Variant) As Variant
Dim i As Integer, k As Integer
Dim FixArr() As Variant, CPArr() As Variant

CPArr = OrArr
ReDim FixArr(UBound(CPArr) - LBound(CPArr) + 1)
k = 1
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)
CleanArrH = FixArr
End Function

Then the vertical one-column code could be

Function CleanArrV(OrArr As Variant) As Variant
Dim i As Long, k As Long
Dim FixArr() As Variant, CPArr() As Variant

CPArr = OrArr
ReDim FixArr(UBound(CPArr) - LBound(CPArr) + 1, UBound(CPArr, 2) -
LBound(CPArr, 2) + 1)
k = LBound(CPArr)
For i = LBound(CPArr) To UBound(CPArr)
If CPArr(i, 1) < "" Then
FixArr(k, LBound(CPArr, 2)) = CPArr(i, LBound(CPArr))
k = k + 1
End If
Next
CleanArrV = FixArr
End Function

I did not reduce the row dimension of the vertical result; that's not
quite as straightforward as the one-dimensional case but is certainly
doable.

I left the code in separate functions, horizontal and vertical, so that
it will be easier to get your head around exactly what's happening in
each case.The above can of course be put into a single function; if you
have any interest in the approach and it's not clear how to combine
them, post back and someone can suggest code to do that.

Alan Beban

Juan Sanchez wrote:

Hi all...
I was trying to write a function that would help me resize
an array in such a way that when I apply the function to
it, it will eliminate the *empty spaces* of the array.

The array I'm trying to modifie is like the one that would
return a formula like =IF(A1:J1=1,A2:J2,"") for each cell
different from 1 on row 1 I would get a empty space in my
array so if I have

A B C D E F G H I J
R1 1 2 1 1 3 2 1 2 2 1
R2 3 4 2 5 7 2 6 4 5 8

my formula would result in {3, ,2,5, , ,6, , ,8}
and after applying my new function which I called
CleanArr, the result would be {3,2,5,6,8}

Here's were I'm at now:
'========================================
Function CleanArr(OrArr As Variant) As Variant
Dim i, j, k As Integer
Dim FixArr(), CPArr() As Variant

CPArr = OrArr
i = 0
For j = 1 To UBound(CPArr())
If CPArr(j) < Empty Then i = i + 1
Next j
ReDim FixArr(i - 1)
j = 1
For k = 0 To UBound(FixArr())
If CPArr(j) < Empty Then
FixArr(k) = CPArr(j)
Else
k = k - 1
End If
j = j + 1
Next k
CleanArr = FixArr
End Function
'========================================

As it is above, it works, if I use:
=CleanArr(IF(A1:J1=1,A2:J2,"")CTR+SHF+ENT, it will return
{3,2,5,6,8}

But... and here's were I'm stuck, if I use it in a column
orientation i.e. =CleanArr(IF(A1:A10=1,B1:B10,""))

It returns #VALUE!

I am LOST how can I make it work in both orientations,
it's intended for single dimension arrays but I want it to
work either in a row or a column...

Any help is greatly appreciated...

TIA
Juan

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?
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?
3.- What is the difference in redim when you use Preserve??

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 also noticed you changed Empty with "", din't now 0 was
concidered empty. I guess is better with "" so that if
the "cell" has a 0 it gets cought.

Thanks again... Juan



-----Original Message-----
First, I would simplify the horizontal one-row code, a la
something like

Function CleanArrH(OrArr As Variant) As Variant
Dim i As Integer, k As Integer
Dim FixArr() As Variant, CPArr() As Variant

CPArr = OrArr
ReDim FixArr(UBound(CPArr) - LBound(CPArr) + 1)
k = 1
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)
CleanArrH = FixArr
End Function

Then the vertical one-column code could be

Function CleanArrV(OrArr As Variant) As Variant
Dim i As Long, k As Long
Dim FixArr() As Variant, CPArr() As Variant

CPArr = OrArr
ReDim FixArr(UBound(CPArr) - LBound(CPArr) + 1, UBound
(CPArr, 2) -
LBound(CPArr, 2) + 1)
k = LBound(CPArr)
For i = LBound(CPArr) To UBound(CPArr)
If CPArr(i, 1) < "" Then
FixArr(k, LBound(CPArr, 2)) = CPArr(i, LBound
(CPArr))
k = k + 1
End If
Next
CleanArrV = FixArr
End Function

I did not reduce the row dimension of the vertical
result; that's not
quite as straightforward as the one-dimensional case but
is certainly
doable.

I left the code in separate functions, horizontal and
vertical, so that
it will be easier to get your head around exactly what's
happening in
each case.The above can of course be put into a single
function; if you
have any interest in the approach and it's not clear how
to combine
them, post back and someone can suggest code to do that.

Alan Beban

Juan Sanchez wrote:

Hi all...
I was trying to write a function that would help me
resize
an array in such a way that when I apply the function
to
it, it will eliminate the *empty spaces* of the array.

The array I'm trying to modifie is like the one that
would
return a formula like =IF(A1:J1=1,A2:J2,"") for each
cell
different from 1 on row 1 I would get a empty space in
my
array so if I have

A B C D E F G H I J
R1 1 2 1 1 3 2 1 2 2 1
R2 3 4 2 5 7 2 6 4 5 8

my formula would result in {3, ,2,5, , ,6, , ,8}
and after applying my new function which I called
CleanArr, the result would be {3,2,5,6,8}

Here's were I'm at now:
'========================================
Function CleanArr(OrArr As Variant) As Variant
Dim i, j, k As Integer
Dim FixArr(), CPArr() As Variant

CPArr = OrArr
i = 0
For j = 1 To UBound(CPArr())
If CPArr(j) < Empty Then i = i + 1
Next j
ReDim FixArr(i - 1)
j = 1
For k = 0 To UBound(FixArr())
If CPArr(j) < Empty Then
FixArr(k) = CPArr(j)
Else
k = k - 1
End If
j = j + 1
Next k
CleanArr = FixArr
End Function
'========================================

As it is above, it works, if I use:
=CleanArr(IF(A1:J1=1,A2:J2,"")CTR+SHF+ENT, it will
return
{3,2,5,6,8}

But... and here's were I'm stuck, if I use it in a
column
orientation i.e. =CleanArr(IF(A1:A10=1,B1:B10,""))

It returns #VALUE!

I am LOST how can I make it work in both orientations,
it's intended for single dimension arrays but I want it
to
work either in a row or a column...

Any help is greatly appreciated...

TIA
Juan
.

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

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
.