I replaced my code with your suggestion as under; but the outcome remains as it was earlier.
I mean, blank rows comes at the top of the range.
Code:
Sub OSumRank()
Sheets("OSum").Select
Application.ScreenUpdating = False
Columns("R:AC").EntireColumn.AutoFit
Dim r As Range
Set r = Range("ae3:ap17")
Const fNum = -9999999.99
With r
On Error Resume Next
.SpecialCells(4).Value = fNum
Range("R3:AC17").Copy
Range("AE3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("AE3:Ap17").Select
Selection.Sort Key1:=Range("AE3"), Order1:=xlAscending, Key2:=Range( _
"AL3"), Order2:=xlDescending, Key3:=Range("AM3"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
.Replace fNum, vbNullString, 1
End With
End Sub
Bookmarks