sorting using macro

[Ross]

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]

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.)

 

[David McRitchie]

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.