Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sorting using macro
I created a series of macros that sort my spreadsheet by a different column heads as needed, and assigned them to buttons, so I can sort by those column when I want to by just clicking. It works fine, until I go and do something else in the sheet, like highlight a range of data for printing. After that, the macro fails and when I click debug, Below is one of the macros that sorts the column headed by names (names is in A1)Any ideas what I can do to fix this? Thanks in advance. Ross Sub sortnames() ' ' sortnames Macro ' Macro recorded 9/14/2005 by ross D ' ' Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub When the macro failed, the entire macro starting with "Selection.Sort Key 1 .. . ." was highlighted in yellow. |
#2
|
|||
|
|||
You're sorting the selected area. So if your selection doesn't include column
A, it'll blow up. I would think it would be better to sort the range you want. Can you pick out a column that always has data in it if that row is used? And you should know the number of columns to sort... Option explicit sub sortnames2() dim RngToSort as range with activesheet set rngtosort = .range("a1:G" & .cells(.rows.count,"A").end(xlup).row) end with with rngtosort .cells.sort Key1:=.columns(1), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom end with end sub I would guess that you know whether you have headers, too. Why let excel guess--it could guess incorrectly. Header:=xlYes or Header:=xlNo. ============== How about another option? I put rectangles over the headers (made the borders invisible) so that when you clicked on the rectangle, it looked like you were clicking on the header. Option Explicit Sub setupOneTime() Dim myRng As Range Dim myCell As Range Dim curWks As Worksheet Dim myRect As Shape Set curWks = ActiveSheet With curWks '10 columns Set myRng = .Range("a1").Resize(1, 10) For Each myCell In myRng.Cells With myCell Set myRect = .Parent.Shapes.AddShape _ (Type:=msoShapeRectangle, _ Top:=.Top, Height:=.Height, _ Width:=.Width, Left:=.Left) End With With myRect .OnAction = ThisWorkbook.Name & "!SortTable" .Fill.Visible = False .Line.Visible = False End With Next myCell End With End Sub Sub sortTable() Dim myTable As Range Dim myColToSort As Long Dim curWks As Worksheet Dim mySortOrder As Long Dim LastRow As Long Set curWks = ActiveSheet With curWks myColToSort = .Shapes(Application.Caller).TopLeftCell.Column LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row Set myTable = .Range("a1:a" & LastRow).Resize(, 10) If .Cells(myTable.Row + 1, myColToSort).Value _ < .Cells(LastRow, myColToSort).Value Then mySortOrder = xlDescending Else mySortOrder = xlAscending End If myTable.Sort key1:=.Cells(myTable.Row, myColToSort), _ order1:=mySortOrder, _ header:=xlYes End With End Sub === Click that invisible rectangle once, it sorts ascending. Click again, descending... (I still used column A to find the last row.) Ross wrote: I created a series of macros that sort my spreadsheet by a different column heads as needed, and assigned them to buttons, so I can sort by those column when I want to by just clicking. It works fine, until I go and do something else in the sheet, like highlight a range of data for printing. After that, the macro fails and when I click debug, Below is one of the macros that sorts the column headed by names (names is in A1)Any ideas what I can do to fix this? Thanks in advance. Ross Sub sortnames() ' ' sortnames Macro ' Macro recorded 9/14/2005 by ross D ' ' Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub When the macro failed, the entire macro starting with "Selection.Sort Key 1 . . ." was highlighted in yellow. -- Dave Peterson |
#3
|
|||
|
|||
Hi Ross,
Did you make a selection before invoking the macro, or are you expecting Excel to expand a single cell and was that single cell in the same current range as A2 -- no intervening empty columns. You can test the current region by selection cell A2 and then using Ctrl+* (shift + asterisk) http://www.mvps.org/dmcritchie/excel/sorting.htm Generally you would select the entire worksheet unless you want to only invove a column so instead of selection. use cells. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ross" wrote in message ... I created a series of macros that sort my spreadsheet by a different column heads as needed, and assigned them to buttons, so I can sort by those column when I want to by just clicking. It works fine, until I go and do something else in the sheet, like highlight a range of data for printing. After that, the macro fails and when I click debug, Below is one of the macros that sorts the column headed by names (names is in A1)Any ideas what I can do to fix this? Thanks in advance. Ross Sub sortnames() ' ' sortnames Macro ' Macro recorded 9/14/2005 by ross D ' ' Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub When the macro failed, the entire macro starting with "Selection.Sort Key 1 . . ." was highlighted in yellow. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting - Macro or worksheet function | Excel Worksheet Functions | |||
Sorting - Macro or worsheet function | Excel Worksheet Functions | |||
Date macro | Excel Discussion (Misc queries) | |||
macro for sorting | Excel Discussion (Misc queries) | |||
Macro for sorting different rows | Excel Worksheet Functions |