I am trying to create a function as follows:
Input: range
Output: column that contains all the non-empty cells in the range.
For example, on input
A | B | C | D
--------------
1 | 2 | | 3
4 | | 5 |
The output should be
A
--
1
2
3
4
5
Here's my trial:
Function toColumn(range As range) As Integer()
Dim res(,) As Integer, i As Integer
i = 0
For Each Row In range.Rows
For Each cel In Row.Cells
If Not IsEmpty(cel) Then
ReDim Preserve res(0, i)
res(0, i) = cel
i = i + 1
End If
Next cel
Next Row
toColumn = res
End Function
I know that a variant of this where res was one dimensional (and thus, the result was a row rather than a column) worked for me. So, the problem is with being it two dimensional.
Also, I know that there's a problem specifically with the decleration
Dim res(,) As Integer
but I can't figure out what's the problem.
Thanks in advance!
Your code is not working due because the
Dim
statement needs to beDim res() As Integer
.However, your code is currently returning a one row and multiple column array, so it needs to be transposed before it can be placed into a column.
Two alternate ways this can be done:
Using an Excel array formula of
and code which just returns a row of information
Note: There is no need to return a two-dimensional array as the
TRANSPOSE
being done by Excel will handle the conversion from a row to a column.Using an Excel array formula of
and code which returns a "two-dimensional" array.
But, because the
ReDim Preserve
can only increase the second dimension (normally the "column" dimension), this requires us to use an array which has columns as the first dimension and rows as the second dimension so that we can increase the rows. The code will then need to perform aTranspose
to get the rows / columns switched back to what they should be. But becauseTranspose
will convert a one-dimensional array into a multi-row, single-column array, again we may as well just use a one dimensional array internally.We will, however, have to return the result as a
Variant
array, as that is whatTranspose
will give us.