TOCOL excel function, but as a UDF in VBA

TOCOL excel function, but as a UDF in VBA ToCol is a function that takes a range and makes it all into one column, similarly toRow also exists. So Excel has the TOCOL function! Yay! It's exactl...

excel

Formulas and Functions

Macros and VBA

HansVogelaar

Oct 13, 2022

Sandeeep

How about

Function TOCOLUMNS(SearchRange As Range)
    Dim v As Variant
    Dim c As Variant
    Dim d As Object
    Dim i As Long
    Dim j As Long
    Dim t As Variant
    Set d = CreateObject("Scripting.Dictionary")
    v = SearchRange.Value
    For Each c In v
        If c <> "" Then
            d(c) = 1
        End If
    Next c
    v = d.Keys
    For i = LBound(v) To UBound(v) - 1
        For j = i + 1 To UBound(v)
            If v(i) > v(j) Then
                t = v(i)
                v(i) = v(j)
                v(j) = t
            End If
        Next j
    Next i
    TOCOLUMNS = Application.Transpose(v)
End Function

Function TOCOLUMNS(SearchRange As Range) Dim v As Variant Dim c As Variant Dim d As Object Dim i As Long Dim j As Long Dim t As Variant Set d = CreateObject("Scripting.Dictionary") v = SearchRange.Value For Each c In v If c <> "" Then d(c) = 1 End If Next c v = d.Keys For i = LBound(v) To UBound(v) - 1 For j = i + 1 To UBound(v) If v(i) > v(j) Then t = v(i) v(i) = v(j) v(j) = t End If Next j Next i TOCOLUMNS = Application.Transpose(v) End Function

Forum Discussion

TOCOL excel function, but as a UDF in VBA

Resources