excel VBA: Alphabetize a multicolumn dynamic named range

[jedwardo]

Hello Everyone,

I have a multicolumn dynamic range linked to a multicolumn combobox on a userform. Am trying to find a way to alphabetize the range top to bottom, left to right as so:

Col1 Col2 Col3

a h o
b i p
c j
d k
e l
f m
g n

Can't find a method that doesn't sort each column separately. My second thought was create one dynamic named range in a single column that will alphabetize and then be the source for the second multicolumn range but couldn't find a way to use a dynamic range as a second dynamic ranges source either.

Any thoughts?

 

[HackSlash]

Your idea is valid. Your main list will be in a "helper column". Put all items in one column and sort it. Then, in the columns where your data currently is become links to the helper column. No need to change or dynamic range or anything else, just change the cells within that range to be links to the helper column instead of the data directly.

 

[Rick Rothstein]

Assuming there are no blank cells within the data in each column, the following macro allows for each column to end at a different row and it will fill those columns down to the last used row within that column with the sorted data downward first before moving to the next column (as your example shows you want it)...

[table="width: 500"]
[tr]
	[td]Sub AlphabetizeMultleColumnsByColumnsFirst()
  Dim C As Long, X As Long, LR As Long, V As Variant, Data As Variant
  Data = Range("A1").CurrentRegion.Value
  With CreateObject("System.Collections.ArrayList")
    For Each V In Data
      If V <> "" Then .Add V
    Next
    .Sort
    Data = Application.Transpose(.ToArray)
    X = 1
    For C = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
      LR = Cells(Rows.Count, C).End(xlUp).Row
      Cells(1, C).Resize(LR) = Application.Index(Data, Evaluate("ROW(" & X & ":" & X + LR - 1 & ")"))
      X = X + LR
    Next
  End With
End Sub[/td]
[/tr]
[/table]