Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting, one last time
Embrassed to ask again but I've tried everything. The code is finally working
as is the command button but it's not sorting Column "D" I need it to sort only Column's A,B,C,D Private Sub CommandButton1_Click() Me.CommandButton1.Caption = "Sort" Dim rng As Range, rng1 As Range With Worksheets("HList") Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp)) Set rng1 = .Cells(3, "A").End(xlToRight) Set rng = rng.Resize(, rng1.Column) rng.Sort _ Key1:=.Range("A3"), _ Order1:=xlAscending, _ Header:=xlNo End With Application.OnTime Now + TimeSerial(0, 0, 2), _ ThisWorkbook.Name & "!ResetCaption" CommandButton1.Caption = "Sorting..." End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting, one last time
If you know it's exactly 4 columns wide, how about:
Set rng = rng.Resize(, 4) And drop the rng1 stuff completely??? Richard wrote: Embrassed to ask again but I've tried everything. The code is finally working as is the command button but it's not sorting Column "D" I need it to sort only Column's A,B,C,D Private Sub CommandButton1_Click() Me.CommandButton1.Caption = "Sort" Dim rng As Range, rng1 As Range With Worksheets("HList") Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp)) Set rng1 = .Cells(3, "A").End(xlToRight) Set rng = rng.Resize(, rng1.Column) rng.Sort _ Key1:=.Range("A3"), _ Order1:=xlAscending, _ Header:=xlNo End With Application.OnTime Now + TimeSerial(0, 0, 2), _ ThisWorkbook.Name & "!ResetCaption" CommandButton1.Caption = "Sorting..." End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting, one last time
as dave mentioned, get rid of the rng1. and if you want to sort by column D,
change the key address Private Sub CommandButton1_Click() Me.CommandButton1.Caption = "Sort" Dim rng As Range, rng1 As Range With Worksheets("HList") Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp)).Resize(, 4) rng.Select rng.Sort Key1:=.Range("A3"), Order1:=xlAscending, Header:=xlNo End With Application.OnTime Now + TimeSerial(0, 0, 2), ThisWorkbook.Name & _ "!ResetCaption" CommandButton1.Caption = "Sorting..." End Sub -- Gary "Richard" wrote in message ... Embrassed to ask again but I've tried everything. The code is finally working as is the command button but it's not sorting Column "D" I need it to sort only Column's A,B,C,D Private Sub CommandButton1_Click() Me.CommandButton1.Caption = "Sort" Dim rng As Range, rng1 As Range With Worksheets("HList") Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp)) Set rng1 = .Cells(3, "A").End(xlToRight) Set rng = rng.Resize(, rng1.Column) rng.Sort _ Key1:=.Range("A3"), _ Order1:=xlAscending, _ Header:=xlNo End With Application.OnTime Now + TimeSerial(0, 0, 2), _ ThisWorkbook.Name & "!ResetCaption" CommandButton1.Caption = "Sorting..." End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting, one last time
Have you tried changing
Key1:=.Range("A3"), _ to Key1:=.Range("D3"), _ "Richard" wrote in message ... Embrassed to ask again but I've tried everything. The code is finally working as is the command button but it's not sorting Column "D" I need it to sort only Column's A,B,C,D Private Sub CommandButton1_Click() Me.CommandButton1.Caption = "Sort" Dim rng As Range, rng1 As Range With Worksheets("HList") Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp)) Set rng1 = .Cells(3, "A").End(xlToRight) Set rng = rng.Resize(, rng1.Column) rng.Sort _ Key1:=.Range("A3"), _ Order1:=xlAscending, _ Header:=xlNo End With Application.OnTime Now + TimeSerial(0, 0, 2), _ ThisWorkbook.Name & "!ResetCaption" CommandButton1.Caption = "Sorting..." End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting, one last time
Thanks, that worked!
"Dave Peterson" wrote: If you know it's exactly 4 columns wide, how about: Set rng = rng.Resize(, 4) And drop the rng1 stuff completely??? Richard wrote: Embrassed to ask again but I've tried everything. The code is finally working as is the command button but it's not sorting Column "D" I need it to sort only Column's A,B,C,D Private Sub CommandButton1_Click() Me.CommandButton1.Caption = "Sort" Dim rng As Range, rng1 As Range With Worksheets("HList") Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp)) Set rng1 = .Cells(3, "A").End(xlToRight) Set rng = rng.Resize(, rng1.Column) rng.Sort _ Key1:=.Range("A3"), _ Order1:=xlAscending, _ Header:=xlNo End With Application.OnTime Now + TimeSerial(0, 0, 2), _ ThisWorkbook.Name & "!ResetCaption" CommandButton1.Caption = "Sorting..." End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Real-time sorting | Excel Discussion (Misc queries) | |||
Sorting time formats | Excel Discussion (Misc queries) | |||
Sorting time in Excel | Excel Discussion (Misc queries) | |||
sorting time format | Excel Discussion (Misc queries) | |||
sorting time in a column | Excel Programming |