Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort breaks when columns are move around
I write lots of simple macros (see below) to sort my data various
ways, but because the sort keys are associated to specific Columns, each time I move columns around, the sort breaks. Is there some way to write the sort such that it will still work after I move around some columns? It is a pain to have to back and manyally update all the cell names. Maybe using the Header Row names? Seems like Excel is so very good at updating dell references when you insert, delete, and move things around that there is probably a better way to do this. Sub SortByStateDisctrictSchool() Cells.Select Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("H2") _ , Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub Using Excel 2000 Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort breaks when columns are move around
How about an alternative?
Use invisible rectangles over the headers that allows you to sort your data based on the column of the rectangle that you clicked. If you want to try that, check out Debra Dalgleish's site: http://contextures.com/xlSort02.html ============ If you're using xl2003+ (IIRC), you could apply data|filter|autofilter. There's an option under the dropdown arrow to sort by this field. ============ But if the header names don't change, you could use them. Option Explicit Sub SortByStateDisctrictSchool2() Dim KeyCol(1 To 3) As Long Dim KeyStr(1 To 3) As String Dim iCtr As Long Dim FoundCell As Range KeyStr(1) = "State" KeyStr(2) = "District" KeyStr(3) = "School" With ActiveSheet With .Rows(1) For iCtr = LBound(KeyStr) To UBound(KeyStr) Set FoundCell = .Cells.Find(What:=KeyStr(iCtr), _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If FoundCell Is Nothing Then 'missing header! MsgBox "Fix the headers and try again!" & vbLf & KeyStr(iCtr) Exit Sub End If KeyCol(iCtr) = FoundCell.Column Next iCtr End With 'data starts in A1 With .UsedRange .Sort key1:=.Columns(KeyCol(1)), order1:=xlAscending, _ key2:=.Columns(KeyCol(2)), order2:=xlAscending, _ key3:=.Columns(KeyCol(3)), order3:=xlAscending, _ header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With End With End Sub livetohike wrote: I write lots of simple macros (see below) to sort my data various ways, but because the sort keys are associated to specific Columns, each time I move columns around, the sort breaks. Is there some way to write the sort such that it will still work after I move around some columns? It is a pain to have to back and manyally update all the cell names. Maybe using the Header Row names? Seems like Excel is so very good at updating dell references when you insert, delete, and move things around that there is probably a better way to do this. Sub SortByStateDisctrictSchool() Cells.Select Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("H2") _ , Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub Using Excel 2000 Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unable to move page breaks | Excel Worksheet Functions | |||
Move page breaks programmatically... | Excel Programming | |||
Move page breaks | Excel Discussion (Misc queries) | |||
why can't I move page breaks? | Excel Discussion (Misc queries) | |||
move page breaks | Excel Worksheet Functions |