Range to Column in Excel using VBA

Question

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!

Answer 1

Your code is not working due because the Dim statement needs to be Dim 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

{=TRANSPOSE(toColumn(A1:D2))}

and code which just returns a row of information

Function toColumn(range As range) As Integer()

    Dim res() As Integer, i As Integer
    i = 0

    For Each cel In range.Cells
         If Not IsEmpty(cel) Then
             ReDim Preserve res(i)
             res(i) = cel
             i = i + 1
        End If
    Next cel

    toColumn = res

End Function

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

{=toColumn(A1:D2)}

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 a Transpose to get the rows / columns switched back to what they should be. But because Transpose 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 what Transpose will give us.

Function toColumn(range As range) As Variant()

    Dim res() As Integer, i As Integer
    i = 0

    For Each cel In range.Cells
         If Not IsEmpty(cel) Then
             ReDim Preserve res(i)
             res(i) = cel
             i = i + 1
        End If
    Next cel

    toColumn = Application.Transpose(res)

End Function