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