In Excel Automatically Sorting Columns

Microsoft Excel 2003 (full product)
February 25, 2011 at 10:45:25
Specs: Windows XP
I've created Metadata in the form of excel spreadsheets for a company, and am wondering if it is possible to create a command or macro of some sort to "auto sort" the contained data whenever new data is entered or updated?

In specific, all of the worksheets include data in Columns A through O (15 columns). Actual data begins in all worksheets on Row 4. Row 3 are headings for the data, though Row 3 contains some merged cells (D through G and K through N). If a range for row usage is needed, then we'll assume 500.

The pertinent column in this case is Column A. Column A contains product names. The original idea proposed to me was to find a way, if possible, that the program would auto-sort the data upon exit. However, from what I've read it seems like it's possible to create a macro that will sort all the data in the worksheet, primarily ascending in (A4:A500) whenever a cell in the previous range is updated, added, or deleted.

Is this possible? If so, what could a possible coding be?

Two variations of answers to others I've found concerning "auto sort" coding (neither work, but I am inserting them here in case they help for ideas) follows as such:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 15 Or Target.Cells.Count > 1 Then Exit Sub
Range("A4:O460").Sort _
Key1:=Range("A4"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 15 Or Target.Cells.Count > 1 Then Exit Sub
Dim SortRange As Range
Set SortRange = Range(("A4"), Cells(Rows.Count, 460).End(xlUp))
SortRange.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlYes
End Sub

I do not fully understand all of the commands used in the macros, and as such I cannot debug them to find out the difference with how they should work for my spreadsheets. However I thought they may be useful as a starting point for creating the sorting codes.


See More: In Excel Automatically Sorting Columns

Report •


#1
February 25, 2011 at 11:28:04
Addendum - I've been trying some more things I've read about, and as such have generated the following coding:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A4:O460").Select
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A4").Select
End Sub

The only problem here is where I designate the final cell selection of A4, which I did otherwise it reselected the entire range of A4:O460 as designated. Is there a different command that can be used to either (a) allow the program to stay with the cell that was just entered, updated, or the cell below one deleted? Or (b) orders the program to go to the newly created or updated cell?

Also, with this coding, every cell in a row other than Column A is triggering the event. How do you fix this so that if anything else is added or updated for existing data in columns B-O, it will not move the cell selection back to A4 and run the sorting by Column A macro?


Report •

#2
February 25, 2011 at 12:41:04
I apologize for continually adding on.

I've updated coding to the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A4:A460")) Is Nothing Then
Range("A4:O460").Select
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Target.Select
End If
End Sub

This fixed the second question I had in my previous post. However, the original remains, of what command do I use so that the originally active cell in Column A (the cell which was added/updated) remains the active cell?

The only two variations I've found are
Range("A4").Select
and
Target.Select
The first sending me to the first cell in the specified range for the worksheep, the second sending me to the last cell in Column A containing information (i.e. the cell above that which had been the active cell).

Is it perhaps possible to assign a value to the updated/added cell, and then tell the coding to "GoTo" that value?

Help's appreciated, thanks!


Report •

Related Solutions


Ask Question