Sort Range Using VBA And Ignoring Or Discarding Blank Rows

[Admin]

Hi

try this one and let me know if you get any message box pop ups ?

Option Explicit

Sub OSumRank()

    Sheets("OSum").Select
    Application.ScreenUpdating = False
    Columns("R:AC").EntireColumn.AutoFit
    
    Dim r       As Range
    Dim bCells  As Range
    
    Range("R3:AC17").Copy
    Range("AE3").Select
    Selection.PasteSpecial Paste:=xlPasteValues
            
    Set r = Range("ae3:ap17")
    
    Const fNum = -9999999.99
    
    With r
        On Error Resume Next
        Set bCells = .SpecialCells(4)
        If Not bCells Is Nothing Then
            MsgBox dcell.Address
        Else
            .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
        End If
        .Replace fNum, vbNullString, 1
    End With

End Sub