Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How can I set up Excel to auto-sort each column independently?

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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 221
Default How can I set up Excel to auto-sort each column independently?

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.So rtFields.Clear
ActiveWorkbook.Worksheets("YourSheetName").Sort.So rtFields.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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 221
Default How can I set up Excel to auto-sort each column independently?

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto data sort by clicking on column Jack C Excel Discussion (Misc queries) 2 August 29th 07 01:02 PM
Excel should treat windows independently like Word. FocusCS Excel Discussion (Misc queries) 0 March 31st 06 12:33 AM
Dynamic column chart - auto sort on data range jimfrog Charts and Charting in Excel 0 March 29th 06 02:45 PM
Is there a way to force Excel to close workbooks independently? Cam Excel Discussion (Misc queries) 4 February 2nd 06 08:43 PM
How to AUTO SORT A-Z new data in a column (not menual sorting) Nir Excel Worksheet Functions 2 November 8th 05 11:29 PM


All times are GMT +1. The time now is 08:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"