New Code Issue

[rmays]

Thanks to everyone who helped my on my last question.

This time my macro keeps getting held up when I get to Selection.Sort:

ActiveWindow.SmallScroll ToRight:=11
ActiveCell.Offset(0, 16).Columns("A:E").EntireColumn.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Cells.Select
Selection.Sort Key1:=ActiveCell.Offset(0, 11).Range("A1"), Order1:= _
xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Does it look like I am doing something wrong? I am just sorting as I normally would with record on.

Thanks in advance for your help.

R.Mays

 

[Jim-in-I]

I am no VBA expert here, but I believe the issue may be caused by the ActiveCell.Offset statement in your code.

If you are trying to sort Column A:E, using the descending values in Column A as the Key, this code should work for you.

Sub SortTest()
 Dim MyRange As Range
 Dim StartCell As Range
 
 Set StartCell = Selection ' Set original cell location
   
 Application.ScreenUpdating = False
 
  With ActiveSheets
   Set MyRange = Range("A1:E65535").End(xlUp)
   MyRange.Sort Key1:=Range("A1"), Order1:=xlDescending, _
                Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
 End With
   
 StartCell.Parent.Select 'Return to original cell
 StartCell.Select
 Application.ScreenUpdating = True
End Sub

Hope it helps,

Jim