Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically sort worksheet column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically sort worksheet column
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 "Paul D." wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically sort worksheet column
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:P" & 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 "Billy Liddel" wrote: 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 "Paul D." wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programatically sort and highlight | Excel Discussion (Misc queries) | |||
How do I sort column changes in different worksheet? | Excel Worksheet Functions | |||
Sort Scenarios in List manually or programatically | Excel Programming | |||
Can I sort a column of #s Rt to left? in a worksheet | Excel Discussion (Misc queries) | |||
Sort by column inside a worksheet | Excel Worksheet Functions |