Sorting a table

[SilverSN95]

Hi, I'm looking for help on a problem that is probably very common but I can't seem to find what I am looking for...
I would like to be able to insert a string into an existing excel table by alphabetical order ascending by column (think insertion sort). If the table is filled, create a new column and place the last element of the order there once sorted.

I'm a newbie with VBA and so I'm not sure what the easiest way to do this would be. Given nothing else, I would just put all of the strings in an array plus the new string to be added, sort that, and overwrite the old table with the contents of the sorted array, but I don't want to write code for something VBA may already do very easily.

Any suggestions or examples would be great, thanks.

[RolfJ]

Place this code into a standard VBA module:


Public Const ROW_TO_SORT_COLUMNS_BY As Long = 2

Sub SortColumns()
Dim r As Range
Set r = ActiveSheet.UsedRange
If r.Cells(1, 1).Row > ROW_TO_SORT_COLUMNS_BY Or r.Cells(r.Rows.Count, 1).Row < ROW_TO_SORT_COLUMNS_BY Then Exit Sub
r.Sort Key1:=Range("B" & ROW_TO_SORT_COLUMNS_BY), _
Order1:=xlAscending, _
Orientation:=xlLeftToRight
End Sub

And now place this code into the VBA module associated with the worksheet containing your table:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row <> ROW_TO_SORT_COLUMNS_BY Then Exit Sub
SortColumns
End Sub

Before trying out the code you need to specify which row you want to order the columns of your table by (by setting the const ROW_TO_SORT_COLUMNS_BY). Once you have done that typing a new value into any cell of that row outside of the original table will cause the column containing the new value to be integrated into the table in the right order.