Sort Range Using VBA And Ignoring Or Discarding Blank Rows

[Admin]

Hi

You can do this in the code itself.

Sub kTest()
    
    Dim r As Range
    
    Set r = Range("a1:k100")
    
    Const fNum = -9999999.99
    
    With r
        On Error Resume Next
        .SpecialCells(4).Value = fNum
        'your code for sort
        .Replace fNum, vbNullString, 1
    End With
    
End Sub

[analyst]

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.

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

[analyst]

Alternatively, is it possible that, after copy, pastevalue, macro can check the range, and if cell found to be empty, clear content, and then sort.

This I'm saying so because, if i manually press 'DEL' button over blank rows at the top, and re-run the macro, result comes at the top, and blank rows remains at the bottom, that is how i want.