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!

Your `Dim` should be `Dim res() As Integer`, but your code is still creating a result that is one row by *x* columns in shape. Are you intending to use this as a UDF, or just as a function in VBA? — YowE3K, Sep 16 '17 at 08:04
@YowE3K so how could I change my code to return a column instead? — Dudi Frid, Sep 16 '17 at 08:06
How to fix it depends on whether the function has to work as a UDF (entered as an "array formula") or whether it is just being used by another piece of VBA code which will write the results back to various cells. — YowE3K, Sep 16 '17 at 08:07

YowE3K · Accepted Answer · 2017-09-16T08:46:41.393

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

score 0 · Answer 2 · answered Sep 16 '17 at 08:38

Try this

Function ToColumn(rng As range) As Variant
    Dim arr
    Dim cel As range
    Dim str As String

    For Each cel In rng
        If Len(cel) > 0 Then
            If str = "" Then
                str = cel
            Else
                str = str & "," & cel
            End If
        End If
    Next cel
    arr = Split(str, ",")
    ToColumn = Application.Transpose(arr)
End Function

How to use this function:
First select the range you think will fit your data, then type =ToColumn(A1:D2) and press Ctrl+Shift+Enter. See image for reference.

Image 1: Entering formula

Image 2: Result

Range to Column in Excel using VBA

2 Answers2