Forum Discussion
TOCOL excel function, but as a UDF in VBA
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
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
Thanks HansVogelaar
This code takes a range and turns it into a single column and adds Unique values.
This is amazing, and what I needed.
I tried editing the code, to make another function.
Where, it just turns everything into a single column, even if its a duplicate.
A simple Range into columns Function.
I tried my best, but couldn't figure it out.
My biggest struggle was the variables. As they were just single letters, I got massively confused and couldn't figure out what variable did what, and how they were used.
If you are free, could you maybe use longer variable names, and add comments? It'll help me learn and make any changes if ever needed.
Btw HansVogelaar
Completely unrelated to this Thread, but I see you help everyone and are super active too.
Could you maybe help me out with my other question?