Programatically sort worksheet column

[Guest]

The challenge I am dealing with is sorting a single column in a worksheet.

I recorded a sort macro using Excel 2007 and it works fine in the program.
When the file is saved in compatibility mode the sort function does not work
when run on a computer with Excel 2002. If I record a sort macro using Excel
2002, the program works fine when using Excel 2002 but will not work when the
program is opened with Excel 2007.

Can anyone help me write code for sorting a single column in a worksheet
that will work in all versions of Excel from 97 to 2007? Any assistance would
be greatly appreciated.

Paul

 

[Guest]

Something like this

Sub SortInsForOut()
' macro to sort deliveries! on 2 two keys
Dim rng
Application.ScreenUpdating = False

Range("A1").Select
rng = Range("a1").CurrentRegion.Address
Range(rng).Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range( _
"D2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

Sheets("DispenseForm").Activate
Application.ScreenUpdating = True
End Sub

The trick is to get the address on each sheet and use this in the sort
macros.
these are the key lines

Range("A1").Select
rng = Range("a1").CurrentRegion.Address
Range(rng).Sort Key1:=Range("B2")

copy these into your macro changing the range to suit.

Peter

 

[Guest]

Perhaps I was a bit vague, but I was trying to get this to work in VB as a
response to a button click as opposed to a macro for a worksheet. Although
what you sent may be helpful I am having difficulty applying it.

The following works in Excel 2007 but not in Excel 2003. Can you tell from
what I have below why it might not be working in the earlier version.

Private Sub CommandButton1_Click()
'sngCountAns is the number of entries contained in the target column
strAddApt = Me.TextBox1.Value
If Len(strAddApt) = 4 Then
Sheets("Lists").Range("P" & sngCountAns + 1).Value = strAddApt
ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Add Key:=Range("P2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Lists").Sort
.SetRange Range("P2" & sngCountAns + 1)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
UserForm_Activate
Me.TextBox1.Value = ""
Else
MsgBox ("Airport ID must be 4 digits to be added to the list")
End If
End Sub