How can I set up Excel to auto-sort each column independently?

[davisjw]

I want to track my DVD collection but I only need a list of titles, sorted
alphabetically. Catalogue software wants to store numerous other attributes
for each title and usually offers a complex interface. But all I want is
titles, so I thought that perhaps I can create a simple list in Excel where I
use columns a-z to store titles alphabetically.

My question is: is it possible to set up my worksheet so that Excel
auto-sorts the content of each column independently of, and without reference
to, other columns?

 

[NoodNutt]

G'day

Try this:

Open the Visual Basic Window

Put this in the "Workbook" section of your spreadsheet

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.OnTime Now + TimeValue("00:30:00"), "AutoSort"
End Sub


Every 30 mins it will "AutoSort" the range you have selected.


Create a Module in the Visual Basic Window.

Call it "AutoSort"

Sub AutoSort()
Range("YourStartRange:YourEndRange").Select
ActiveWorkbook.Worksheets("YourSheetName").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("YourSheetName").Sort.SortFields.Add
Key:=Range("YourStartRange:YourEndRange"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("YourSheetName").Sort
.SetRange Range("YourStartRange:YourEndRange")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

The (Add Key:=Range) is the column which you want to sort.

You can also place a macro button on your spreadsheet so that you can
manually trigger the sort.

HTH
Mark

 

[NoodNutt]

Just to clarify my previous post.

List all your DVD titles in one column, this will enable you to keep track
of how many you have, and if you choose later, somewhere down the track, you
can enter additional info about the movies, eg Stars, Director, Your Rating
on it, Who you lent it to, etc etc............................

HTH
Mark.