Sorting, one last time

[Richard]

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]

If you know it's exactly 4 columns wide, how about:

Set rng = rng.Resize(, 4)

And drop the rng1 stuff completely???

 

[Gary Keramidas]

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

 

[PY & Associates]

Have you tried changing
Key1:=.Range("A3"), _

to
Key1:=.Range("D3"), _

 

[Richard]

Thanks, that worked!

If you know it's exactly 4 columns wide, how about:

Set rng = rng.Resize(, 4)

And drop the rng1 stuff completely???